Double VLOOKUP

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

Hi,
Could someone help me out with the following:
I would like to lookup IR00202PH in column A and subsequently check if
column B contains an "S" and column C contains "FIXED". If both
lookups are true, then again lookup IR00202PH in column A and retrieve
the corresponding value from column D as a result. If false take the
value from column E.
Thanks a lot !!
Regards,
Robert
 
=IF(ISNA(MATCH(1,(A1:A20="IR00202PH")*(B1:B20="S")*(C1:C20="FIXED"),0)),
VLOOKUP("IR00202PH",A:E,5,FALSE),
INDEX(D:D,MATCH(1,(A1:A20="IR00202PH")*(B1:B20="S")*(C1:C20="FIXED"),0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Robert said:
Hi,
Could someone help me out with the following:
I would like to lookup IR00202PH in column A and subsequently check if
column B contains an "S" and column C contains "FIXED". If both
lookups are true, then again lookup IR00202PH in column A and retrieve
the corresponding value from column D as a result. If false take the
value from column E.
Thanks a lot !!
Regards,
Robert
Click to expand...

{=IF((A1:A9="IR00202PH")*(B1:B9="S")*(C1:C9="FIXED"),D1:D9,E1:E9)}
FormulaArray

Bruno
 
Back
Top