Try Several Lookups with an IFERROR Chain

Excel Formulas › Information

All versionsIFERROR

Search one table, and if the value isn’t there, fall through to the next — and the next. Nested IFERROR chains multiple lookups so the first hit wins and a clean message shows if all miss.

Quick formula: look in table 1, else table 2, else a default:
=IFERROR(VLOOKUP(id,T1,2,0), IFERROR(VLOOKUP(id,T2,2,0), "Not found"))
Each IFERROR catches the previous lookup’s #N/A and tries the next source.

Functions used (tap for the full reference guide):

IFERRORVLOOKUP

The example

The ID is found in the second table.

AB
1LookupResult
2X-200from Table 2

The formula

The formula:

=IFERROR(VLOOKUP(id,T1,2,0), IFERROR(VLOOKUP(id,T2,2,0), "Not found")) // first hit wins

How it works

How it works:

  1. The inner VLOOKUP runs first; if it finds the ID, that value is returned.
  2. If it errors (#N/A), the surrounding IFERROR catches it and runs the next lookup.
  3. Nest as many as you need; the final argument is the fallback message when everything misses.
  4. Order matters — put the most authoritative source first so it wins ties.

Cleaner on 365: stack the sources and let one XLOOKUP search them with its built-in not-found argument, or use =IFNA(...) instead of IFERROR when you specifically want to catch only #N/A (a real #VALUE! from bad data should probably surface, not be swallowed).

Try it: interactive demo

Live demo

Look up an ID across two tables.

Result:

Variations

IFNA instead

Catch only #N/A:

=IFNA(VLOOKUP(id,T1,2,0), VLOOKUP(id,T2,2,0))

Three sources

Chain deeper:

=IFERROR(L1, IFERROR(L2, IFERROR(L3, "—")))

365 stacked

One XLOOKUP:

=XLOOKUP(id, VSTACK(k1,k2), VSTACK(v1,v2), "Not found")

Pitfalls & errors

IFERROR hides everything. It catches #VALUE!/#REF! too — a typo in a table reference fails silently. Use IFNA to catch only true misses.

Order = priority. The first lookup that succeeds wins; arrange sources accordingly.

Exact match. Use FALSE/0 as the 4th VLOOKUP argument or you may get a wrong, non-erroring hit.

Practice workbook

📊
Download the free Try Several Lookups with an IFERROR Chain practice workbook
An IFERROR-chain sheet with the IFNA, three-source, and 365-stacked variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I try multiple lookups in Excel if the first fails?
Nest them in IFERROR: =IFERROR(VLOOKUP(id,T1,2,0), IFERROR(VLOOKUP(id,T2,2,0), "Not found")). Each IFERROR catches the prior miss and tries the next table.
Should I use IFERROR or IFNA?
IFNA catches only #N/A, so genuine errors like #VALUE! still surface. IFERROR catches every error and can hide real problems.
How do I add a third source?
Nest another IFERROR: =IFERROR(L1, IFERROR(L2, IFERROR(L3, "—"))).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: IFERROR handling · IFNA handling · Multi-sheet lookup

Function references: IFERROR · VLOOKUP