How to Use XLOOKUP in Excel

Find a value in one column and return the matching cell from another with XLOOKUP, including a not-found message and multi-column results.

You have a product name in one cell and a price list on another sheet. XLOOKUP finds the name in the list and returns the price next to it.

Type the formula

=XLOOKUP(E2, A2:A50, C2:C50)
  • lookup_value is what to search for, here the product name in E2.
  • lookup_array is the column to search, here the product names in A2:A50.
  • return_array is the column to return from, here the prices in C2:C50.

If E2 holds Desk lamp and the list has Desk lamp at 24.99, the cell shows 24.99. XLOOKUP matches exactly by default.

The full syntax has three optional arguments:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
  • if_not_found is what to show when nothing matches.
  • match_mode is 0 for exact (default), -1 for the next smaller value, 1 for the next larger, 2 for wildcards.
  • search_mode is 1 to search from the top (default) or -1 to search from the bottom.

Handle a missing value

Add a fourth argument so a miss shows a message instead of #N/A:

=XLOOKUP(E2, A2:A50, C2:C50, "Not found")

Look up in any direction

The return column can sit to the left of the lookup column. To find a product name from a SKU stored in column C:

=XLOOKUP(E2, C2:C50, A2:A50, "Not found")

Return several columns at once

Give a return array that is more than one column wide:

=XLOOKUP(E2, A2:A50, B2:D50, "Not found")

The result spills into three cells to the right, one for each column in B2:D50. Keep those cells empty or the formula shows a #SPILL! error.

Find the last match

Set search_mode to -1 when the list has duplicates and the most recent entry is at the bottom:

=XLOOKUP(E2, A2:A50, C2:C50, "Not found", 0, -1)

If XLOOKUP shows as #NAME?. The function exists only in Microsoft 365 and Excel 2021 or later. In older versions, use INDEX and MATCH for the same result: =INDEX(C2:C50, MATCH(E2, A2:A50, 0)).

If the lookup column has stray spaces. Wrap the value in TRIM, as in =XLOOKUP(TRIM(E2), A2:A50, C2:C50), or clean the list with Data, Text to Columns first.

More Excel how-tos