Double VLOOKUP

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robert - 03 Mar 2008 09:14 GMT

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

Reply to this Message

Bob Phillips - 03 Mar 2008 09:53 GMT

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

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi,
> Could someone help me out with the following:
[quoted text clipped - 6 lines]
> Regards,
> Robert

Reply to this Message

Bruno Campanini - 03 Mar 2008 17:59 GMT

> Hi,
> Could someone help me out with the following:
[quoted text clipped - 6 lines]
> Regards,
> Robert

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

Bruno

Reply to this Message

Rate this thread: