To do an XLOOKUP in Excel, type =XLOOKUP(lookup_value, lookup_array, return_array). For example, =XLOOKUP(F2,A2:A100,C2:C100) looks for the value in F2 in column A and returns the matching value from column C. XLOOKUP looks for an exact match by default, can look left as well as right, and lets you set your own message when nothing is found. It’s available in Microsoft 365, Excel 2021, Excel 2024 and Excel for the web, but not in Excel 2019 or earlier. Below is the full syntax, worked examples and fixes for common errors.
XLOOKUP syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | What it means |
|---|---|
| lookup_value | The value you’re looking for, such as a product ID in F2. |
| lookup_array | The column or row to search, such as A2:A100. |
| return_array | The column or row to return a value from. It must be the same size as lookup_array. |
| if_not_found | Optional. Text or a value to show instead of #N/A. |
| match_mode | Optional. 0 = exact match (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard. |
| search_mode | Optional. 1 = first to last (default), -1 = last to first, 2 and -2 = fast binary search on sorted data. |
Step by step: your first XLOOKUP
In this example, column A holds employee IDs, column B holds names and column C holds departments. You want to type an ID in F2 and see the department in G2.
- Click cell G2.
- Type
=XLOOKUP(. - Click F2 (the ID to find) and type a comma.
- Select A2:A100 (the IDs) and type a comma.
- Select C2:C100 (the departments).
- Type
)and press Enter.
The finished formula is =XLOOKUP(F2,A2:A100,C2:C100). Change the ID in F2 and the result updates.
If you’ll copy the formula down, lock the ranges with dollar signs, for example =XLOOKUP(F2,$A$2:$A$100,$C$2:$C$100). See how to keep one cell constant in Excel.
Useful XLOOKUP examples
Show a message instead of #N/A
=XLOOKUP(F2,A2:A100,C2:C100,"Not found")
The fourth argument replaces the IFERROR wrapper you’d need with VLOOKUP.
Look to the left
=XLOOKUP(F2,C2:C100,A2:A100)
This finds a value in column C and returns the matching value from column A. VLOOKUP can’t do this.
Return several columns at once
=XLOOKUP(F2,A2:A100,B2:D100)
The result spills across three cells, returning the name, department and any other columns in that range. Make sure the cells to the right are empty.
Find the last match
=XLOOKUP(F2,A2:A100,C2:C100,,0,-1)
Setting search_mode to -1 searches from the bottom up. That’s handy for finding the most recent order for a customer in a list sorted by date. The two commas skip the if_not_found argument.
Approximate match for grades, tax bands or commission rates
With score thresholds in E2:E6 (0, 60, 70, 80, 90) and grades in F2:F6 (F, D, C, B, A):
=XLOOKUP(B2,$E$2:$E$6,$F$2:$F$6,,-1)
A score of 84 returns B, because 80 is the next smaller threshold. Unlike VLOOKUP, the table doesn’t need to be sorted for this to work.
Wildcard match
=XLOOKUP("*"&F2&"*",B2:B100,C2:C100,"None",2)
This finds the first name that contains the text in F2. Use * for any number of characters and ? for a single character.
Two-way lookup (row and column)
With months across B1:M1, products down A2:A20 and sales in the grid:
=XLOOKUP(H2,A2:A20,XLOOKUP(H3,B1:M1,B2:M20))
The inner XLOOKUP picks the month’s column, and the outer one picks the product’s row.
Look up from another sheet
=XLOOKUP(A2,Prices!A:A,Prices!C:C,"No price")
For a full walkthrough, see how to use XLOOKUP with two sheets.
More things XLOOKUP can do
Return a blank instead of 0
If the matching cell is empty, XLOOKUP returns 0. To show a blank instead, add an empty string: =XLOOKUP(F2,A2:A100,C2:C100)&"". This turns the result into text, so don’t use it when you need to do maths on the result.
Look up a date
Dates are numbers in Excel, so XLOOKUP matches them like any other value. If your lookup cell holds a date and time but the list holds dates only, strip the time first with =XLOOKUP(INT(F2),A2:A100,C2:C100). To find the closest earlier date, such as the exchange rate on or before a given day, use match_mode -1.
Sum everything between two lookups
XLOOKUP returns a cell reference, so you can use two of them to build a range. =SUM(XLOOKUP(G1,A2:A13,B2:B13):XLOOKUP(G2,A2:A13,B2:B13)) adds up the values from the month in G1 to the month in G2.
Compare two lists
To check which items in one list are missing from another, use =XLOOKUP(A2,Sheet2!A:A,Sheet2!A:A,"Missing") and copy it down. Every “Missing” result is an item that isn’t on the second sheet. For other methods, see how to compare two sheets in Excel for matches.
Tips for faster, safer lookups
- Use tables. Turn your data into a table with Ctrl + T, then refer to columns by name, for example
=XLOOKUP(F2,Staff[ID],Staff[Department]). The ranges grow automatically as you add rows. - Avoid full-column references on huge sheets. A:A works, but limited ranges or table columns calculate faster in large workbooks.
- Clean your data first. Most lookup problems come from stray spaces or numbers stored as text, not from the formula.
- Use binary search only on sorted data. search_mode 2 or -2 is very fast, but it returns wrong answers if the list isn’t sorted.
XLOOKUP vs VLOOKUP
| Feature | XLOOKUP | VLOOKUP |
|---|---|---|
| Default match | Exact | Approximate, unless you add FALSE |
| Look left | Yes | No |
| Column number needed | No, you select the return column | Yes, so it breaks if columns are inserted |
| Built-in “not found” message | Yes | No, needs IFERROR |
| Search from the bottom | Yes | No |
| Works in Excel 2019 and older | No | Yes |
If you share files with people on older versions of Excel, use VLOOKUP or INDEX/MATCH instead. See how to do a VLOOKUP in Excel.
Is XLOOKUP available in Excel 2019?
No. XLOOKUP is included in Microsoft 365, Excel 2021, Excel 2024, Excel for the web and the Excel mobile apps. Excel 2019, 2016 and earlier don’t have it. If you open a workbook containing XLOOKUP in one of those versions, the formula shows #NAME?. In Excel 2019, use =INDEX(C2:C100,MATCH(F2,A2:A100,0)), which works the same way as a basic XLOOKUP, including looking to the left.
Fix common XLOOKUP errors
#N/A
No match was found. Check for extra spaces with =TRIM(), numbers stored as text (or text stored as numbers), and typos. Add an if_not_found value to show a friendlier message.
#VALUE!
The lookup_array and return_array are different sizes, for example A2:A100 and C2:C99. Make them match.
#NAME?
Your version of Excel doesn’t support XLOOKUP, or the function name is misspelled.
#SPILL!
The result needs more than one cell, but something is in the way. Clear the cells the result needs, or return a single column.
Wrong result with approximate match
Check the match_mode. Use -1 for “this value or the next smaller” and 1 for “this value or the next larger”.
Frequently asked questions
Can XLOOKUP return multiple matches?
No, it returns the first (or last) match only. To return every match, use =FILTER(C2:C100,A2:A100=F2).
Can XLOOKUP look up using two criteria?
Yes. Multiply the conditions: =XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:C100).
Is XLOOKUP case-sensitive?
No. “apple” and “APPLE” count as the same value.
Summary
- Type
=XLOOKUP(what, where, return). - Add a fourth argument for a custom “not found” message.
- Use match_mode -1 or 1 for approximate matches, and search_mode -1 to find the last match.
- Use INDEX/MATCH or VLOOKUP if people will open the file in Excel 2019 or older.
Related: How to Merge Two Tables in Excel.
Related: how to use a formula in Excel.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.