Lookup with multiple lookup values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I set up a lookup formula that uses two lookup values? I am trying to
do a formula that has one absolute value (it will always equal "A"), but each
row will have a variable value ("1", "2", etc.). So I have a column that
each cell will reference the absolute value of "A" and the variable value of
"1", "2", etc. If the corresponding sheet that I am pulling the information
from matches both "A" and "7", for instance, it will return the value that
relates to these two lookup values. Also, I was wanting to find out how to
have a lookup function return a blank cell or an amount of $0 if there is
nothing that matches to the given criteria. I am getting a value input on
the ones that should be blank of another cell's information. Let me know if
my questions are confusing or if you need further clarification. Thanks.
 
Hi
first try the array formula (entered with cTRL+sHIFT+eNTER):
=INDEX('sheet1'!C1:C100,MATCH(1,('sheet1'!A1:A100="A")*('sheet1'!B1:B10
0=7),0))

For the second one use the array formula:
=IF(ISNA(MATCH(1,('sheet1'!A1:A100="A")*('sheet1'!B1:B100=7),0)),0,INDE
X('sheet1'!C1:C100,MATCH(1,('sheet1'!A1:A100="A")*('sheet1'!B1:B100=7),
0)))

--
Regards
Frank Kabel
Frankfurt, Germany

Aaron said:
How do I set up a lookup formula that uses two lookup values? I am trying to
do a formula that has one absolute value (it will always equal "A"), but each
row will have a variable value ("1", "2", etc.). So I have a column that
each cell will reference the absolute value of "A" and the variable value of
"1", "2", etc. If the corresponding sheet that I am pulling the information
from matches both "A" and "7", for instance, it will return the value that
relates to these two lookup values. Also, I was wanting to find out how to
have a lookup function return a blank cell or an amount of $0 if there is
nothing that matches to the given criteria. I am getting a value input on
the ones that should be blank of another cell's information. Let me know if
my questions are confusing or if you need further clarification.
Click to expand...
Thanks.
 
Frank - Thanks so much for your help. I will try this out today and
hopefully it will work. Aaron
 
Back
Top