vlookup with two lookup values?

K

Kim B.

I am not sure if this is possible or not. I have an extensive list of data
in which one column I have dates and the next column I have a unit location
number. I was wondering if it would be possible using a vlookup to return
values for a specific date for a specific unit location number.
 
T

T. Valko

Try this array formula** :

column A = dates
column B = unit location number
column C = data to return

E1 = lookup date
F1 = lookup unit location number

=INDEX(C1:C100,MATCH(1,(A1:A100=E1)*(B1:B100=F1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
K

Kim B.

Thank you that did work. If you don't mind I would like to ask you one more
related question. Right now the formula returns a n/a if there was not any
data for a location on a specific day, is there a way to change it so it
would return '0' in those cases?
 
T

T. Valko

Still an array formula:

=IF(ISNA(MATCH(1,(A1:A100=E1)*(B1:B100=F1),0)),0,INDEX(C1:C100,MATCH(1,(A1:A100=E1)*(B1:B100=F1),0)))
 
K

Kim B.

Thank you, you saved me a ton of time.


T. Valko said:
Still an array formula:

=IF(ISNA(MATCH(1,(A1:A100=E1)*(B1:B100=F1),0)),0,INDEX(C1:C100,MATCH(1,(A1:A100=E1)*(B1:B100=F1),0)))
Click to expand...
 
Top