Double lookup condition required

[heatwave]

I am having trouble with the attached excel file. In simple terms, the user needs to fill in 2, non related values (day and time) and Excel is suppose to find the unique combination and return 4 individual values which is unique to that date and time.

I have tried INDEX with double MATCH commands and it refuses to work . I prefer not to use VLOOKUP as the data need to be arranged in ascending order and I don't think it is possible. The date is in ascending order but the time cycles every new day (6 hour periods).

Any assistance would be highly appreciated.

[DonkeyOte]

Based on your file

D19: =LOOKUP(2,1/(($B$4:$B$11=$B19)*($C$4:$C$11=$C19)),D$4:D$11)
copied across to G19

(if you're running XL2007 you could use SUMIFS)

[heatwave]

Thank you DonkeyOte! Thats perfect - exactly what I was looking for.

For my future understanding (rather than just copying someone else's code) - I understand the part after "2,1/", however looking at Microsoft Office Help lookup menu - I could not find the meaning of the 2, 1 and the "/" divide sign.

Can you pl shed a little light on what the parameters mean?

Many thanks again

[DonkeyOte]

There have been a few explanations of the 2/1 method recently, daddylonglegs put a nice one together yesterday... I did one a while back and though the syntax of the test itself is not the same (ie that after the 1/... ) the general premise behind the method is identical, hopefully you will be able to make sense of it from that ?

http://www.excelforum.com/excel-2007...-function.html

(post #11 onwards really)