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!

 

[Milo_Minderbinder]

the answer to all questions like this is usually "Sumproduct".

Book1
	A	B	C	D	E	F	G
1	1/4/2000	8:15	99		1/4/2000	8:15	99
2	1/4/2000	8:20	98		1/5/2000	8:15	95
3	1/4/2000	8:25	97		1/6/2000	8:15	0
4	1/4/2000	8:30	96		1/7/2000	8:15	91
5	1/5/2000	8:15	95
6	1/5/2000	8:20	94
7	1/5/2000	8:25	93
8	1/5/2000	8:30	92
9	1/7/2000	8:15	91
10	1/7/2000	8:20	90
11	1/7/2000	8:25	89
12	1/7/2000	8:30	88
Sheet2

 

[uberathlete]

Thanks Milo! Just wondering, how would I modify the formula such that when any of the conditions are met, #N/A would appear. Cuz currently 0 appears, but the price could be 0 so it's hard to distinguis which ones don't actually satisfy the combined date and time conditions.

 

[Brian from Maui]

Try,

=INDEX($C$2:$C$11,MATCH(1,INDEX(($A$2:$A$11=E1)*($B$2:$B$11=F1),0,1),0))

 

[Milo_Minderbinder]

use

=IF(SUMPRODUCT(($A$1:$A$12=E1)*($B$1:$B$12=F1))=0,"N/A",SUMPRODUCT((($A$1:$A$12=E1)*($B$1:$B$12=F1)),$C$1:$C$12))

The first sumproduct only counts the occurances, if zero, returns N/A, if not then sums appropriate values in column C.

 

[Brian from Maui]

Edit my above formula to,

=INDEX($C$2:$C$11,MATCH(1,INDEX(($A$2:$A$11=E1)*($B$2:$B$11=F1),0,1),#N/A))

Change the #N/A to whatever you want, but enclose in quotes if not an error message or text.

As a personal preference, SUMPRODUCT "should" be used for multi conditional counting/summing.

 

[uberathlete]

Thank you so much. You folks are the best!

 

[Yogi Anand]

Hi uberathlete:

Following is another ...

Book2
	A	B	C	D	E	F	G	H
1	Date	Time	Price			ReqDate	ReqTime	ReqPrice
2	1/4/2000	8:15:00	99.00			1/4/2000	8:25	97.00
3	1/4/2000	8:20:00	98.00			1/5/2000	8:25	76.00
4	1/4/2000	8:25:00	97.00			1/7/2000	8:25	#N/A
5	1/4/2000	8:30:00	90.00
6	1/5/2000	8:35:00	87.00
7	1/5/2000	8:20:00	94.00
8	1/5/2000	8:25:00	76.00
9	1/5/2000	8:30:00	89.00
10	1/7/2000	10:40:00	99.00
11	1/7/2000	10:45:00	90.00
12	1/7/2000	10:50:00	91.00
Sheet2

array formula in cell H2 is ...

=LOOKUP(G2,IF(F2=$A$2:$A$12,$B$2:$B$12),$C$2:$C$12)

this is then copied down.

 

[Yogi Anand]

And this one uses a non-array formula ...

Book2
	A	B	C	D	E	F	G	H
1	Date	Time	Price			ReqDate	ReqTime	ReqPrice
2	1/4/2000	8:15:00	99.00			1/4/2000	8:25	97.00
3	1/4/2000	8:20:00	98.00			1/5/2000	8:25	76.00
4	1/4/2000	8:25:00	97.00			1/7/2000	8:25	#N/A
5	1/4/2000	8:30:00	90.00
6	1/5/2000	8:35:00	87.00
7	1/5/2000	8:20:00	94.00
8	1/5/2000	8:25:00	76.00
9	1/5/2000	8:30:00	89.00
10	1/7/2000	10:40:00	99.00
11	1/7/2000	10:45:00	90.00
12	1/7/2000	10:50:00	91.00
Sheet2 (3)

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.

 

[Guentjo]

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.