Vlookup or other formula to return data given criteria

Hi all,

I was wondering if any one can provide any advise on a formula to find value 'X' (table to the bottom) from the data table (table on top), I have used Vlookup but unfortunately it only allows you to search from 1 criteria. As you can see below in order for X to be correct, the criteria needs to include Start/End/Date then should this match in the data table(top), the value next to start/end/date (table to the top) should be returned. In the case of 01/02/2011, 00:30:00 01:00:00 - x should be 15.

start End Date No
00:30:00 01:00:00 01/02/2011 15
01:30:00 02:00:00 01/02/2011 14
04:00:00 04:30:00 01/02/2011 15
05:00:00 05:30:00 01/02/2011 1
06:00:00 06:30:00 01/02/2011 8
06:30:00 07:00:00 01/02/2011 5
00:30:00 01:00:00 02/02/2011 5
01:30:00 02:00:00 02/02/2011 5
04:00:00 04:30:00 02/02/2011 4
05:00:00 05:30:00 02/02/2011 78
06:00:00 06:30:00 02/02/2011 112
06:30:00 07:00:00 02/02/2011 15

01/02/2011 02/02/2011
00:30:00 01:00:00 x x
01:30:00 02:00:00 x x
04:00:00 04:30:00 x x
05:00:00 05:30:00 x x
06:00:00 06:30:00 x x
06:30:00 07:00:00 x x



Any advice is greatly appreciated

One way to do this is to create a 'helper' column in both of your tables which contains the two dates concatenated together. This effectively merges them into a single value and you can then use this in the VLOOKUP formula.

Hi - maybe if you were to upload your spreadsheet it may help. Also, in the sheet describe your desired output

Originally Posted by Blake 7

Hi - maybe if you were to upload your spreadsheet it may help. Also, in the sheet describe your desired output

Please find attached, its a tricky one re attached: call.xls

ok - thanks for the upload, saves me typing out the table. Just to be sure ive got it - G2 and 3 should be 15 and 14 and cells h2 and 3 should both be 5........ correct?

Hiya - am working on it......... may have to get back to you tomorrow

EDIT: ive uploaded the spreadsheet for you.

Hi Had - sorry about the delay - nearly there........ ok, as per your spreadsheet

H2 use CSE to enter then copy down
=INDEX($D$2:$D$13,MATCH(1,($A$2:$A$13=F2)*($B$2:$B$13=G2)*($C$2:$C$13=$H$1),0))

and in I2 again use CSE to enter then copy down
=INDEX($D$2:$D$13,MATCH(1,($A$2:$A$13=F2)*($B$2:$B$13=G2)*($C$2:$C$13=$I$1),0))

Hope this helps you out..

Hi glad to hear it's working fine.

btw - the solution I offered is one way of doing it (it works)! however, there are probably more efficient ways out there to be explored........

re your questions, please find a link below which will tell you all you need to know circa Index Match.

http://www.contextures.com/xlfunctions03.html

Please mark your thread as solved.

Cheers

The only issue with this is I have to select specific cells on the data table in order for it to return the values

I'm not sure what you mean.......

Can you advise whether it is possible to select lookup for the whole of column A lets say instead of A2:A19

Yes, here's the code

=INDEX($D:D,MATCH(1,(A:A=F2)*(B:B=G2)*(C:C=$H$1),0))

I have entered: D:D instead but theres a #Num error when I try and replace it?

In an array formula all lookup arrays need to be the same length.

If this clears everything up please mark as solved.