Multiple Value Vlookup?

uberathlete

Board Regular
Joined
Jul 11, 2007
Messages
117
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):

6hf7o04.jpg


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!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Milo_Minderbinder

Well-known Member
Joined
Mar 2, 2006
Messages
548
the answer to all questions like this is usually "Sumproduct".
Book1
ABCDEFG
11/4/20008:15991/4/20008:1599
21/4/20008:20981/5/20008:1595
31/4/20008:25971/6/20008:150
41/4/20008:30961/7/20008:1591
51/5/20008:1595
61/5/20008:2094
71/5/20008:2593
81/5/20008:3092
91/7/20008:1591
101/7/20008:2090
111/7/20008:2589
121/7/20008:3088
Sheet2
 

uberathlete

Board Regular
Joined
Jul 11, 2007
Messages
117
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.
 

Milo_Minderbinder

Well-known Member
Joined
Mar 2, 2006
Messages
548
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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

Board Regular
Joined
Jul 11, 2007
Messages
117
Thank you so much. You folks are the best!
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi uberathlete:

Following is another ...
Book2
ABCDEFGH
1DateTimePriceReqDateReqTimeReqPrice
21/4/20008:15:0099.001/4/20008:2597.00
31/4/20008:20:0098.001/5/20008:2576.00
41/4/20008:25:0097.001/7/20008:25#N/A
51/4/20008:30:0090.00
61/5/20008:35:0087.00
71/5/20008:20:0094.00
81/5/20008:25:0076.00
91/5/20008:30:0089.00
101/7/200010:40:0099.00
111/7/200010:45:0090.00
121/7/200010:50:0091.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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
And this one uses a non-array formula ...
Book2
ABCDEFGH
1DateTimePriceReqDateReqTimeReqPrice
21/4/20008:15:0099.001/4/20008:2597.00
31/4/20008:20:0098.001/5/20008:2576.00
41/4/20008:25:0097.001/7/20008:25#N/A
51/4/20008:30:0090.00
61/5/20008:35:0087.00
71/5/20008:20:0094.00
81/5/20008:25:0076.00
91/5/20008:30:0089.00
101/7/200010:40:0099.00
111/7/200010:45:0090.00
121/7/200010:50:0091.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.
 

Forum statistics

Threads
1,181,719
Messages
5,931,656
Members
436,797
Latest member
fullmetalpogi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top