Skip to content
ExcelExcel 2021+

XLOOKUP function in Excel

Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

XLOOKUP syntax and parameters

Six arguments, three required.

  • lookup_valueanyRequired

    The value to search for *If omitted, XLOOKUP returns blank cells it finds in lookup_array.

  • lookup_arrayarrayRequired

    The array or range to search

  • return_arrayarrayRequired

    The array or range to return

  • if_not_foundanyOptional

    Where a valid match is not found, return the [if_not_found] text you supply.If a valid match is not found, and [if_not_found] is missing, #N/A is returned.

  • match_modenumberOptional

    Specify the match type:0 - Exact match. If none found, return #N/A. This is the default.-1 - Exact match. If none found, return the next smaller item.1 - Exact match. If none found, return the next larger item.2 - A wildcard match where *, ?, and ~ have special meaning.

  • search_modenumberOptional

    Specify the search mode to use:1 - Perform a search starting at the first item. This is the default.-1 - Perform a reverse search starting at the last item.2 - Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.-2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

XLOOKUP examples

Formulas you'll actually reuse.

  1. Price lookup that says so when the product is missing:

    =XLOOKUP(E2, Products[SKU], Products[Price], "Not stocked")

    Result24.99

  2. Search from the bottom to get the latest order:

    =XLOOKUP(E2, Orders[Customer], Orders[Date], , 0, -1)

    Result2026-08-14

XLOOKUP in Google Sheets

Same name — your formula ports as-is.

Try XLOOKUP in the playground

Edit the example — nothing to install.

Preloaded with the XLOOKUP formula from Example 1 — change anything and watch it respond.

Loading the editor…

XLOOKUP errors

What they mean — and the fixes.

  • #N/A

    No match and no if_not_found fallback — pass a fourth argument so missing lookups read as data, not errors.

  • #VALUE!

    lookup_array and return_array have different lengths — both must span the same number of rows or columns.

Related functions

More ways Excel gets this done.