Excel - Double VLOOKUP

Asked By Robert on 05-Mar-08 05:31 AM
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




Bob Phillips replied on 03-Mar-08 04:53 AM
=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)
Bruno Campanini replied on 03-Mar-08 12:59 PM
{=IF((A1:A9="IR00202PH")*(B1:B9="S")*(C1:C9="FIXED"),D1:D9,E1:E9)}
FormulaArray

Bruno