XLOOKUP is Excel’s modern lookup function. It searches a range or array and returns the item that matches, and it fixes the biggest annoyances of VLOOKUP: the return column can sit on either side of the lookup column, and you no longer count column numbers. This guide covers the syntax, every argument, and practical patterns you can copy.
The syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | Required? | What it does |
|---|---|---|
| lookup_value | Yes | The value to search for |
| lookup_array | Yes | The range or array to search |
| return_array | Yes | The range or array that supplies the result |
| if_not_found | No | Text to return when there is no valid match. If omitted, you get #N/A |
| match_mode | No | How matches are made (see below) |
| search_mode | No | The direction or method of searching (see below) |
match_mode values
| Value | Behavior |
|---|---|
| 0 | Exact match; #N/A if none is found (default) |
| -1 | Exact match, or the next smaller item |
| 1 | Exact match, or the next larger item |
| 2 | Wildcard match, where *, ? and ~ have special meaning |
search_mode values
| Value | Behavior |
|---|---|
| 1 | Search first to last (default) |
| -1 | Reverse search, last to first |
| 2 | Binary search; the lookup array must be sorted ascending |
| -2 | Binary search; the lookup array must be sorted descending |
Binary modes return invalid results if the data isn’t sorted correctly, so only use them on data you know is sorted.
Example 1: a basic exact lookup
With country names in A2:A20 and telephone codes in C2:C20, this returns the code for the country typed in F2:
=XLOOKUP(F2, A2:A20, C2:C20)
Exact match and first-to-last searching are the defaults, so you only need the first three arguments.
Example 2: show a friendly message instead of #N/A
=XLOOKUP(F2, A2:A20, C2:C20, "Not found")
The fourth argument replaces the #N/A error with your own text, which is far tidier than wrapping the formula in IFERROR.
Example 3: return several columns at once
Because the return array can be more than one column, a single formula can return, for example, an employee’s name and department together:
=XLOOKUP(F2, A2:A20, B2:C20)
Example 4: find the closest match
For tiers such as tax or discount bands, use an approximate match. Setting match_mode to 1 returns the exact match or the next larger item; -1 returns the next smaller one:
=XLOOKUP(F2, A2:A10, B2:B10, 0, 1)
Example 5: a two-way lookup
Nest one XLOOKUP inside another to look up a row and a column at the same time, a job often done with INDEX and MATCH. The inner function picks the row, and the outer one picks the column:
=XLOOKUP(H1, B1:E1, XLOOKUP(G2, A2:A10, B2:E10))
Example 6: find the last match
Set search_mode to -1 to search from the bottom up, handy when a value appears more than once and you want the most recent entry:
=XLOOKUP(F2, A2:A100, C2:C100, "Not found", 0, -1)
XLOOKUP vs. VLOOKUP
| XLOOKUP | VLOOKUP | |
|---|---|---|
| Lookup and return ranges | Separate arrays | One table plus a column index number |
| Return column position | Either side of the lookup column | Lookup works left to right only |
| Default match | Exact | Approximate unless you specify |
| Not-found handling | Built-in argument | Requires IFERROR |
Availability and pitfalls
- Versions: XLOOKUP isn’t available in Excel 2016 or 2019. Those versions may open a workbook created in a newer version that contains it, but you can’t write new XLOOKUP formulas there.
- Sorting: never use the binary search modes on unsorted data.
- Wildcards: they only work when match_mode is 2.
- Keep ranges the same size: the lookup and return arrays should line up row for row.
