Cellwise Try it free

Lookup function

Excel XLOOKUP function

XLOOKUP searches one range for a value and returns the matching item from another range. It defaults to an exact match, can look left or right, and has a built-in "not found" value. It replaces VLOOKUP, HLOOKUP, and most INDEX/MATCH combos.

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

Arguments

lookup_value
The value to find.
lookup_array
The column or row to search.
return_array
The column or row to return from. It must be the same size as lookup_array.
if_not_found
What to return if there is no match. Leave it out to get #N/A.
match_mode
0 for exact (default), -1 for exact or next smaller, 1 for exact or next larger, 2 for wildcards.
search_mode
1 searches first to last (default), -1 searches last to first.

XLOOKUP examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

ABCD
1Emp IDNameDeptSalary
2E-104Rosa DiazSales62000
3E-117Ken ItoMarketing71500
4E-122Ava StoneSales58000
5E-130Malik ReedFinance80250
  1. =XLOOKUP("E-117",A2:A5,B2:B5)

    Result: Ken Ito

    Finds E-117 and returns the name: "Ken Ito".

  2. =XLOOKUP("Ava Stone",B2:B5,A2:A5)

    Result: E-122

    Looks left: searches names and returns the ID "E-122".

  3. =XLOOKUP("E-999",A2:A5,B2:B5,"No match")

    Result: No match

    The fourth argument replaces #N/A with "No match".

  4. =XLOOKUP("Sales",C2:C5,B2:B5,,0,-1)

    Result: Ava Stone

    Search mode -1 starts from the bottom, so it returns the last Sales person: "Ava Stone".

How to use XLOOKUP

  1. Type =XLOOKUP( and the value to find.
  2. Select the column to search in.
  3. Select the column to return from.
  4. Optionally add a "not found" message, then close the bracket.

Common XLOOKUP mistakes

Ranges of different sizes

lookup_array and return_array must be the same height. A2:A5 with B2:B6 gives #VALUE!.

Not available in older Excel

XLOOKUP needs Microsoft 365 or Excel 2021 and later. Shared files opened in Excel 2016 will show #NAME?.

Using binary search on unsorted data

Match modes -1 and 1 work on unsorted data, but search_mode 2 or -2 (binary search) needs sorted data. Leave search_mode at its default unless you need it.

XLOOKUP FAQ

What is the difference between XLOOKUP and VLOOKUP?

XLOOKUP uses separate lookup and return ranges, so it can look left. It also defaults to exact match and has a built-in "not found" argument.

How do I return multiple columns with XLOOKUP?

Make return_array several columns wide, like B2:D5. In Excel 365 the results spill across into neighboring cells.

How do I find the last match with XLOOKUP?

Set search_mode to -1: =XLOOKUP(A2,B:B,C:C,,0,-1). It searches from the bottom up.

Does XLOOKUP work in Excel 2016 or 2019?

No. It is only in Microsoft 365, Excel 2021 and later, and Excel for the web.

Practice XLOOKUP in these lessons

Guides that use XLOOKUP

Related functions

Updated