FormulaGym
XP 0
Streak 0

The XLOOKUP function in Excel

XLOOKUP finds a value in one range and returns the matching item from another. It is the modern replacement for VLOOKUP.

What XLOOKUP does

You point it at the cells to search and the cells to return. They can be anywhere, so it can look to the left, which VLOOKUP cannot. It matches exactly by default, and it has a built-in option for what to show when nothing is found. It is available in Microsoft 365, Excel 2021 and later, and Excel for the web.

Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
lookup_value
What to find.
lookup_array
The cells to search.
return_array
The cells to return from. It must be the same size as the lookup array.
if_not_found
Optional. What to show when there is no match.

Worked example

ABCDEF
1IDProductPriceLook up productID
21001Notebook8Backpack1007
31004Desk lamp25
41007Backpack40
51010Headphones60
F2 =XLOOKUP(E2,B2:B5,A2:A5)

The ID is to the left of the product name, and XLOOKUP still finds it.

Now you try

A fresh exercise every time. Press Another exercise as often as you like.

Where you will use it

Sales & Ops

Find an ID or a price from a name.

Finance

Match references across two lists.

Data cleanup

Check whether an entry exists in another list.

Common mistakes

  • Using it in Excel 2019 or earlier, where it is not available.
  • Search and return ranges of different sizes, which causes a #VALUE! error.
  • Forgetting the not-found argument, so a missing value shows #N/A.

The XLOOKUP course

  1. 1
    Look up an ID by nameFree practice below
    Practise
  2. 2
    Look left and rightComing soon
  3. 3
    Handle missing matchesComing soon
  4. 4
    Return a whole rowComing soon
  5. 5
    Search from the bottom upComing soon
  6. 6
    Replace VLOOKUP in old workbooksComing soon

Common questions

Is XLOOKUP better than VLOOKUP?

For most jobs, yes. It can look in any direction, it matches exactly by default, and it does not break when you insert columns.

Which versions of Excel have XLOOKUP?

Microsoft 365, Excel 2021 and later, and Excel for the web. It is not in Excel 2019 or earlier.

Related functions