XLOOKUP in Excel: A Beginner’s Guide With Examples
XLOOKUP is Excel’s modern lookup function: it searches one range for a value and returns the matching item from another. The basic formula is =XLOOKUP(lookup_value, lookup_array, return_array). It defaults to an exact match, can look left or right, and lets you set your own “not found” message. It works in Excel 2021, Excel 2024 and Microsoft 365.
Key takeaways
- XLOOKUP needs only three arguments and uses an exact match by default.
- Unlike VLOOKUP, it can return values from columns to the left and doesn’t break when you insert columns.
- The optional
if_not_foundargument replaces wrapping formulas in IFERROR. - It can return several columns at once and handle multiple criteria with a simple array trick.
- Excel 2016 and 2019 can’t create XLOOKUP formulas; you need Excel 2021, 2024 or Microsoft 365.
In this guide
XLOOKUP formula syntax explained
XLOOKUP has three required arguments and three optional ones: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). For most everyday lookups you only need the first three, which tell Excel what to find, where to look and what to bring back.
| Argument | Required? | What it does |
|---|---|---|
lookup_value |
Yes | The value you want to find, such as a product code in cell F2 |
lookup_array |
Yes | The column or row to search |
return_array |
Yes | The column or row to return a result from; it can be on either side |
if_not_found |
No | Text or a value to show when there’s no match (otherwise #N/A) |
match_mode |
No | 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard |
search_mode |
No | 1 first to last (default), -1 last to first, 2 and -2 binary search on sorted data |
The full definitions are on Microsoft’s XLOOKUP function page. If you’re new to formulas generally, our guide to Excel formulas for beginners covers cell references and the $ sign, which you’ll use here.
Your first XLOOKUP: a step-by-step example
Suppose column A holds product codes, column B product names and column C prices. To find the price of the code typed in F2, enter =XLOOKUP(F2, A2:A20, C2:C20) in G2. Excel finds the code in column A and returns the price from the same row in column C.
| A: Code | B: Product | C: Price (£) | |
|---|---|---|---|
| 2 | P-101 | Wireless mouse | 18.50 |
| 3 | P-102 | USB-C hub | 34.00 |
| 4 | P-103 | Laptop stand | 27.99 |
- Type the code you want to look up, for example
P-102, in cell F2. - Click G2 and type
=XLOOKUP(. - Click F2, then type a comma. This is the lookup value.
- Select A2:A20, then type a comma. This is the lookup array.
- Select C2:C20. This is the return array.
- Add
, "Not found"if you want a friendly message instead of #N/A, then close the bracket and press Enter.
The finished formula is =XLOOKUP(F2, A2:A20, C2:C20, "Not found") and returns 34.00. To get the product name instead, change the return array to B2:B20. If you plan to copy the formula down, lock the ranges with $, as in $A$2:$A$20.

XLOOKUP vs VLOOKUP: what’s the difference?
XLOOKUP does everything VLOOKUP does with fewer pitfalls. It uses an exact match by default, looks in any direction, doesn’t rely on a column number that breaks when columns move, and handles missing values without IFERROR. VLOOKUP is still useful for files shared with people on Excel 2019 or earlier.
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Default match | Approximate (unless you add FALSE) | Exact |
| Look left | No | Yes |
| Return column | Fixed number (e.g. 3) | A range, so it survives inserted columns |
| Not-found message | Needs IFERROR or IFNA | Built-in if_not_found |
| Search from the bottom | No | Yes (search_mode -1) |
| Horizontal lookups | Needs HLOOKUP | Same function |
| Return several columns | One formula per column | One formula spills across |
| Excel versions | All versions | Excel 2021, 2024, Microsoft 365, Excel for the web |
The VLOOKUP equivalent of our earlier example is =VLOOKUP(F2, A2:C20, 3, FALSE). Forget the FALSE and VLOOKUP may silently return the wrong price, which is the single most common lookup mistake we see in shared spreadsheets.
Practical XLOOKUP examples
Once the basic pattern clicks, small changes to the optional arguments cover most real tasks: looking left, returning whole rows, finding the latest entry, matching partial text and working out bands such as tax rates or commission tiers.
Look up a value to the left
To find the code for a product name, search column B and return column A: =XLOOKUP("Laptop stand", B2:B20, A2:A20). VLOOKUP can’t do this without rearranging columns.
Return several columns at once
=XLOOKUP(F2, A2:A20, B2:C20) returns both the product name and the price; the results spill into the cells to the right. Make sure those cells are empty, or you’ll see a #SPILL! error.
Find the most recent entry
In a log where the same customer appears many times, =XLOOKUP(F2, A2:A500, D2:D500, , 0, -1) searches from the bottom up and returns the latest matching row.
Approximate match for bands and tiers
With sales thresholds in E2:E6 (0, 1000, 5000…) and commission rates in F2:F6, =XLOOKUP(B2, E2:E6, F2:F6, , -1) returns the rate for the highest threshold that doesn’t exceed the sales figure. Unlike VLOOKUP’s approximate mode, the table doesn’t have to be sorted for match modes -1 and 1.
Wildcard match
=XLOOKUP("*hub*", B2:B20, C2:C20, , 2) finds the first product whose name contains “hub”. Use * for any characters and ? for a single character.
XLOOKUP with multiple criteria
To match on two or more conditions, multiply the tests together and look up the value 1: =XLOOKUP(1, (A2:A100=G2)*(B2:B100=H2), C2:C100). Each test produces TRUE or FALSE; multiplying them gives 1 only on rows where every condition is met.
For example, with region in column A, product in column B and sales in column C, put “North” in G2 and “USB-C hub” in H2. The formula returns the sales figure for that region and product combination.
- Add more conditions by multiplying further brackets, such as
*(D2:D100=I2). - All ranges must be the same size, or you’ll get #VALUE!.
- For “either/or” logic, add the tests instead of multiplying, and look up a value greater than zero with
=XLOOKUP(TRUE, ((A2:A100="North")+(A2:A100="South"))>0, C2:C100).
If you find yourself building many multi-criteria lookups across linked tables, that’s often a sign the data has outgrown a spreadsheet; our explainer on what Microsoft Access is used for covers when a database makes more sense.
Still on Excel 2016 or 2019?
Those versions can open files containing XLOOKUP but can’t create new XLOOKUP formulas, and both left Microsoft support in October 2025 (see our Office 2019 support status guide). A one-off Excel 2024 for Windows licence adds XLOOKUP and dynamic arrays without a subscription, or compare the full suites in our Office 2024 range.
Common XLOOKUP errors and how to fix them
Most XLOOKUP problems come from four causes: no matching value (#N/A), ranges of different sizes (#VALUE!), blocked spill cells (#SPILL!) or an older Excel version (#NAME?). The fixes are quick once you know which error you’re looking at.
| Error | Likely cause | Fix |
|---|---|---|
| #N/A | No match found; often extra spaces or numbers stored as text | Add if_not_found; clean data with TRIM; convert text to numbers |
| #VALUE! | Lookup and return arrays are different sizes | Make both ranges start and end on the same rows |
| #SPILL! | Cells where results need to spill aren’t empty | Clear the cells to the right or below |
| #NAME? | Excel version doesn’t support XLOOKUP, or a typo | Check spelling; use Excel 2021, 2024 or Microsoft 365 |
| Wrong result | Ranges shifted when copied down | Lock ranges with $ |
A hidden trap is numbers stored as text, for instance codes imported from another system. “101” as text won’t match 101 as a number. Use Data > Text to Columns > Finish on the column, or wrap the lookup value in TEXT() or VALUE() so both sides match.
Frequently asked questions
Which Excel versions have XLOOKUP?
XLOOKUP is available in Excel 2021, Excel 2024, Microsoft 365, Excel for the web and the current mobile apps. Excel 2016 and 2019 can open a workbook that already contains XLOOKUP formulas, but they can’t create new ones and will show #NAME? if you try to type the function.
Is XLOOKUP better than VLOOKUP?
For most tasks, yes. XLOOKUP defaults to an exact match, can look left, survives inserted columns and has a built-in not-found message. VLOOKUP is still worth knowing if you share workbooks with people on older Excel versions, because it works everywhere while XLOOKUP needs Excel 2021 or later.
Can XLOOKUP return multiple values?
It can return several columns from one match by selecting a wider return array, such as B2:D20, and the results spill to the right. To return every row that matches, rather than just the first, use the FILTER function instead, which is designed for multiple matching results.
How do I use XLOOKUP with two criteria?
Multiply two logical tests and look up 1, for example =XLOOKUP(1,(A2:A100=G2)*(B2:B100=H2),C2:C100). Rows meeting both conditions evaluate to 1, so XLOOKUP returns the first of them. All ranges must be the same size, and you can add further conditions by multiplying more tests.
Why is my XLOOKUP returning #N/A?
Excel couldn’t find an exact match. Common reasons are extra spaces, numbers stored as text, slightly different spellings or a lookup range that doesn’t cover all the data. Check the values match exactly, clean them with TRIM if needed, and add an if_not_found message to handle genuinely missing items.
Does XLOOKUP work in Google Sheets?
Yes. Google Sheets added an XLOOKUP function with the same core arguments, so most formulas transfer between the two. Behaviour around array formulas and some options can differ slightly, so test any complex lookups after moving a workbook between Excel and Sheets.
The bottom line
XLOOKUP replaces VLOOKUP, HLOOKUP and most INDEX/MATCH combinations with one clear formula: what to find, where to look, what to return. Start with the three required arguments, add if_not_found for tidy results, and use the multiply trick for multiple criteria. For more spreadsheet basics, read our Excel tips for beginners.
Useful links
| Resource | Why it helps |
|---|---|
| Excel formulas for beginners | Cell references and formula basics |
| Excel tips for beginners | Everyday spreadsheet skills |
| What is Microsoft Access used for | When data outgrows a spreadsheet |
| Excel 2024 for Windows | Standalone Excel with XLOOKUP |
| Office 2024 range | Full suites including Excel 2024 |
| Microsoft Support: XLOOKUP function | Official syntax and argument values |
| Microsoft Support: FILTER function | Return every matching row |
| Office 2019 support status | Why older Excel versions lack XLOOKUP and support |