# Multiple Value Vlookup?

#### uberathlete

##### Board Regular
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!

### 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
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
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

##### MrExcel MVP
Try,

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

#### Milo_Minderbinder

##### Well-known Member
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
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
Thank you so much. You folks are the best!

#### Yogi Anand

##### MrExcel MVP
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
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.

#### Guentjo

##### Active Member
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.

Replies
0
Views
261
Replies
10
Views
675
Replies
0
Views
203
Replies
5
Views
342
Replies
5
Views
801

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.

### Which adblocker are you using?

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

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