# Multi-lookup

1. ## Multi-lookup

Hello all,

I'm having trouble figuring out how to lookup multiple values.

In the attached file, I have a look up in place to return values from the "data" tab based on the date entered in B4 in the "summary" tab.

This works fine and I use this method on a larger scale for other sets of data but I now want to return a sum of values for a range of user selected dates.

On the right hand side of the "summary" tab is pretty much what I'm after. The user will input a from and to date (1-31) and I need a formula that looks up all the data for those dates.

All help greatly appreciated!!

Thanks.

2. ## Re: Multi-lookup

Hello,

change your data labels in Summary!J7:J9 to be the same text as Data!A6:A8, namely

Time run
Breakdown
Output

Then use in Summary cell M7:

``Please Login or Register  to view this content.``
copy down.

The expected result for Output in your sample data is wrong. If you want to sum up days 1 to 5 it should result in 47,500, not 32.5.

Alternatively, you have some explaining to do as to how to arrive at 32.5.

cheers

3. ## Re: Multi-lookup

you could try in m7
=SUM(OFFSET(Data!B6,,\$K\$4-1,,\$N\$4-\$K\$4+1))

4. ## Re: Multi-lookup

Please let me know if this is ok
regards
Peter
PS I also changed some of the data in row 13 just to make sure the formula works, so thisis why it is different to what you originally posted.

5. ## Re: Multi-lookup

Wow!

Thank you all for your replies!

Firstly let me apologise for the lack of consistency in the original spreadsheet. I cobbled it together quite quickly so rushed a few things!

peterjuhnke - thanks for the time and effort with the spreadsheet. This is a good solution but would involve redesigning the data tab and as we have 20 lines this would be quite time consuming!

martindwilson - this seems to work when I tested it but teylyn got there first!

Thanks teylyn - this worked like a dream and I've been able to apply the formula to the other lines and fields I need to total - perfect!!

I've not used match, index or offset functions before so I will be having a play around with these to see if they can help with other problems.

Thanks again, I'd never have cracked this on my own!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1