Skip to content

XLOOKUP with a "not found" message

The modern replacement for VLOOKUP: look up in any column, return from any column, with a built-in fallback.

The formula

Argument separator
Excel and Google Sheets · Excel 365 and Excel 2021
=XLOOKUP(A2, Products!A2:A100, Products!C2:C100, "Not found")

Available in Google Sheets

For Excel 2019 and older
=IFERROR(INDEX(Products!C2:C100, MATCH(A2, Products!A2:A100, 0)), "Not found")

How it works

  1. A2

    The value to find.

  2. Products!A2:A100

    The column to search in.

  3. Products!C2:C100

    The column to return a value from. It can be left or right of the search column.

  4. "Not found"

    Shown instead of #N/A when there is no match.

Example

A: CodeB: Result (price)
P-10112.50
P-20589.00
P-999Not found

Tips

  • XLOOKUP matches exactly by default, so there is no FALSE argument to forget.
  • Both ranges must be the same size.