XLOOKUP: The Modern Lookup Formula

Excel & Google Sheets

How it works

XLOOKUP searches A2:A10 for the value in E2 and returns the value in the same position from B2:B10. If there's no match, it returns "Not found" instead of an #N/A error. Because the lookup column and return column are separate arguments, the return column can be anywhere — left or right of the lookup column — and inserting new columns never breaks the formula. Exact match is the default, so there's no FALSE argument to forget.

E2

The value to find.

A2:A10

The column to search.

B2:B10

The column to return values from. Must be the same size as the search column.

"Not found"

Optional: what to show when there's no match, instead of #N/A.

When to use it

Use XLOOKUP as your default lookup in Excel 2021+, Microsoft 365, and Google Sheets. Fall back to INDEX + MATCH only when you must support Excel 2019 or older.

Common mistakes

• ✕Search and return ranges are different sizes.

  ✓A2:A10 with B2:B99 returns #VALUE!. Both ranges must cover the same rows.

• ✕Using it in a workbook others open in old Excel.

  ✓XLOOKUP needs Excel 2021 or Microsoft 365. For older versions, use INDEX + MATCH.