Search code examples
excelvlookup

VLOOKUP - if nothing found, look for another value in different range

I'm trying to lookup the value in C2, if nothing is found in range "AK:AL" I want to lookup the value in E2 from range AN:AO instead (both from sheet "X")

I tried this formula but I get strange results.. =IF(ISNA(VLOOKUP(C2,X!AK:AL,2)),VLOOKUP(E2,X!AN:AO,2,0),VLOOKUP(C2,X!AK:AL,2))

Solution

  • You should use approximate match false (or 0) in all your lookups.

    Also shorter formula:

    =IFERROR(VLOOKUP(C2,X!AK:AL,2,0),VLOOKUP(E2,X!AN:AO,2,0))

    If error is found in first vlookup, it looks at second.