Lookup with multiple lookup values

[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.

 

[Frank Kabel]

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

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.

 

[Guest]

Frank - Thanks so much for your help. I will try this out today and
hopefully it will work. Aaron