Excel Practice Sheets

XLOOKUP Formula Generator

Fill in what you are looking for and where to find it, and get a working XLOOKUP formula with the fallback that stops it returning #N/A.

The cell or value to search for, such as A2.

Where to search. Lock it with $ signs so it survives a fill-down.

The column holding the answer. Same height as the lookup range.

Shown instead of #N/A when nothing matches.

When to use XLOOKUP

  • Pulling a price, region or name out of a reference table
  • Replacing a VLOOKUP that breaks whenever a column is inserted
  • Returning a value from a column to the left of the one you match on

How the formula works

Taking the example on this page apart, piece by piece:

=XLOOKUP(A2,$F$2:$F$5,$G$2:$G$5,"Not Found")

  1. A2

    The value to look for: whatever is in A2.

  2. $F$2:$F$5

    Where to look for it. XLOOKUP searches this range for an exact match.

  3. $G$2:$G$5

    What to return: the value from this range on the same row as the match.

  4. "Not Found"

    What to show when there is no match, instead of #N/A.

Where it goes wrong

  • Leaving the ranges unlocked

    It works on the first row, then shifts when filled down and starts returning #N/A for the earliest records. Lock them with $ signs.

  • Lookup and return ranges of different heights

    XLOOKUP returns #VALUE! when the two arrays do not cover the same number of rows. This is invisible until it runs.

  • Skipping the fourth argument

    Without an if-not-found value, a missing match shows #N/A. A readable message is almost always better in a report.

Practise it

A generator gives you the formula. These give you the reason it works — and tell you which assumption broke when it does not.

Questions

Do I need a recent version of Excel for XLOOKUP?

XLOOKUP needs Excel 2021 or Microsoft 365. In older versions, INDEX and MATCH do the same job — and you will still meet them in other people's files, so both are worth knowing.

Why use XLOOKUP instead of VLOOKUP?

VLOOKUP counts columns from the left edge of one block, so inserting a column silently breaks it. XLOOKUP takes the lookup range and the return range as separate arguments, so it does not care where they sit or which direction they run.

Can I look up a value to the left?

Yes. The return range is independent of the lookup range, so it can be any column in either direction. That is the main thing VLOOKUP cannot do.
Practise XLOOKUP Learn XLOOKUP