How to Use XLOOKUP in Excel: A Beginner's Guide
XLOOKUP is the modern replacement for VLOOKUP and HLOOKUP. It's easier to write, it looks in any direction, and it does an exact match by default. If you have a recent version of Excel, it's the lookup function you should learn first.
Which Excel versions have XLOOKUP?
XLOOKUP is available in Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. It is not available in Excel 2019, 2016 or older. If you open a file that uses XLOOKUP in one of those versions, the formula won't work. In that case, use INDEX and MATCH instead.
XLOOKUP syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Only the first three arguments are required:
- lookup_value – what you're looking for.
- lookup_array – the column (or row) to search in.
- return_array – the column (or row) to return a value from.
Unlike VLOOKUP, you don't count columns. You simply point to the column to search and the column to return.
Basic example
An employee list has names in column A, departments in column B and email addresses in column C. To find Maria's department:
=XLOOKUP("Maria", A2:A50, B2:B50)Or, with the name in cell F2:
=XLOOKUP(F2, A2:A50, B2:B50)Look to the left
With VLOOKUP, the column you search must be the first one. XLOOKUP doesn't care. To find the name that belongs to an email address (searching column C, returning column A):
=XLOOKUP(F2, C2:C50, A2:A50)Show a message when nothing is found
The fourth argument replaces the #N/A error with anything you like:
=XLOOKUP(F2, A2:A50, B2:B50, "Not found")No need to wrap the formula in IFERROR or IFNA.
Return several columns at once
If return_array spans more than one column, XLOOKUP returns all of them and the result "spills" into the neighbouring cells:
=XLOOKUP(F2, A2:A50, B2:C50)This returns both the department and the email for the name in F2. Leave the cells to the right empty, or you'll get a #SPILL! error.
Match mode and search mode
The last two arguments are optional but useful.
| match_mode | Meaning |
|---|---|
0 (default) | Exact match |
-1 | Exact match, or the next smaller value |
1 | Exact match, or the next larger value |
2 | Wildcard match (*, ? and ~) |
| search_mode | Meaning |
|---|---|
1 (default) | Search from first to last |
-1 | Search from last to first (returns the last match) |
2 / -2 | Binary search on data sorted ascending / descending |
Example: find a tax rate or discount band
If column A lists order thresholds (0, 100, 500, 1000) and column B the matching discount, this finds the discount for the amount in F2 by taking the next smaller threshold:
=XLOOKUP(F2, A2:A5, B2:B5, , -1)An order of 640 matches the 500 row. Note the empty fourth argument: you can skip if_not_found by leaving a blank between the commas.
Example: get the most recent entry
In a log where the same customer appears many times, searching from the bottom returns their latest record:
=XLOOKUP(F2, A2:A500, C2:C500, "No orders", 0, -1)XLOOKUP vs VLOOKUP
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Default match | Approximate (you must add FALSE) | Exact |
| Look to the left | No | Yes |
| Breaks when a column is inserted | Yes (column number changes) | No |
| Built-in "not found" value | No | Yes |
| Return several columns | No | Yes |
| Works in Excel 2019 and older | Yes | No |
Still working with VLOOKUP in Google Sheets? Our VLOOKUP guide covers it in detail, and Google Sheets supports XLOOKUP too.
Frequently asked questions
Why does XLOOKUP return #VALUE!?
Most often the lookup and return arrays have different sizes, for example A2:A50 and B2:B40. Make both ranges cover the same rows.
Is XLOOKUP case-sensitive?
No. To make a case-sensitive lookup, combine it with EXACT: =XLOOKUP(TRUE, EXACT(A2:A50, F2), B2:B50).