What is XLOOKUP in Excel?
Quick answer
XLOOKUP is an Excel function that searches one column (or row) for a value and returns the matching item from another, for example the price for a product code. Its basic form is =XLOOKUP(what to find, where to look, what to return), with an optional message if nothing matches.
Last updated
Updated · By Robert Breen
Why it matters
Copying a price, a customer’s account manager or an employee’s department from one list into another by hand is slow and error-prone. A lookup does it for every row at once and stays right when the source list changes.
XLOOKUP replaces the older VLOOKUP and HLOOKUP for most jobs. It can look left as well as right, it matches exactly by default (VLOOKUP does not) and it lets you say what to show when there is no match, instead of an #N/A error.
A worked example
A hardware store’s order sheet lists product codes in column A. The price list on another tab has codes in column A and prices in column C. In the order sheet, =XLOOKUP(A2, Prices!A:A, Prices!C:C, "Not found") returns the price for each code. Code HX-220 returns $14.95; a mistyped code returns "Not found" instead of an error.
When the supplier raises HX-220 to $15.95, the buyer changes one cell on the price list and every open order updates. Before, someone retyped prices into each order and two orders a week went out at the old price.
Often confused with
XLOOKUP vs VLOOKUP
VLOOKUP only looks to the right of the first column, needs a column number that breaks when columns are inserted, and defaults to an approximate match. XLOOKUP has none of those traps.
XLOOKUP vs a filter
XLOOKUP returns one matching value per row (the first match). To see every matching row, use a filter or the FILTER function.
XLOOKUP vs a join
In databases and BI tools, a join combines whole tables on a shared key. XLOOKUP does a small version of that one column at a time.
Tips
- Always fill in the "if not found" argument so missing values are obvious.
- Look up on a unique ID (a code), not on a name that could be spelled two ways.
- Clean stray spaces in codes first; "HX-220 " will not match "HX-220".
Related terms
Learn it in a free lesson
- Excel for the Web Tutorial: Your First Budget Spreadsheet (about 15 minutes)
Sources
Written from these official help pages. Menus change, so if a button has moved, the help page has the current wording.
Frequently asked questions
- Which versions of Excel have XLOOKUP?
- Excel for Microsoft 365, Excel for the web and Excel 2021 or later. Excel 2016 and 2019 do not have it.
- Why does XLOOKUP return #N/A?
- No exact match was found and no "if not found" value was given. Check for extra spaces, numbers stored as text, or a code that really is missing.
- Does Google Sheets have XLOOKUP?
- Yes. Google Sheets has an XLOOKUP function with very similar arguments.
All microsoft 365 terms, A to Z · Free microsoft 365 lessons