Multiple Value Vlookup?

[uberathlete]

Hi everyone, I really need some help. Is there such a thing as a multiple value Vlookup? Basically, here's my problem (please refer to image):

On the left are columns for Date, Time and Price. On the right, there is a blank column for Req Price which has to be filled with the corresponding price based on the Req Date and Req Time. So for cell H2, there should be the price that corresponds to 1/4/00 and 8:25:00, which would be 97.00 (which comes from cell C4). If say there is no price that corresponds then the cell is simply filled with an #N/A (which would be the case for cell H4). Then so on and so forth for the remaining cells in column H.

Is there like a Vlookup function wherein you can specify 2 lookup values or something? Or should a macro be used for this sort of thing? Any suggestions or recommendations would be much appreciated. Thanks!

 

[Yogi Anand]

formula in cell H2 is ...

=LOOKUP(2,1/(($A$2:$A$12=F2)*($B$2:$B$12=G2)),$C$2:$C$12)

this is then copied down.

Please to explain Yogi.

Hi Guentjo:

Here we go ...

1. =($A$2:$A$12=F2)*($B$2:$B$12=G2)
creates an array ={0;0;1;0;0;0;0;0;0;0;0}

2. =1/(($A$2:$A$12=F2)*($B$2:$B$12=G2))
creates an array ={#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}

3. =LOOKUP(2,1/(($A$2:$A$12=F2)*($B$2:$B$12=G2)),$C$2:$C$12)
matches 2 with the 3rd value in array in 2. above and then extracts 97.00 from array C2:C12