XLOOKUP is the modern replacement for VLOOKUP in Excel, and it fixes almost every frustration that comes with using the older formula. It is simpler to write, more flexible, and doesn’t have the same blind spots. If you’re on Excel 365 or Excel 2021, XLOOKUP is the lookup formula you should be using for new spreadsheets. This guide explains how it works, what makes it better, and how to use its more advanced options.
Why XLOOKUP Was Created
VLOOKUP has been part of Excel for decades, but it has two well-known problems. First, it can only look to the right — your search column must always be to the left of the column you want to return. Second, it references return columns by number (e.g. column 3 of a range), which breaks silently if someone inserts a new column into the table.
XLOOKUP addresses both issues. You specify the lookup column and the return column as separate ranges, so you can look left, right, or even across sheets. And because you point at the exact column you want, inserting columns elsewhere doesn’t affect your formula.
The Three Required Arguments
The basic XLOOKUP syntax is:
=XLOOKUP(lookup_value, lookup_array, return_array)
1. lookup_value
The value you’re searching for — the same as VLOOKUP’s first argument. This can be a typed value, a cell reference, or a formula result.
2. lookup_array
The single column (or row) you want to search. This is just the search column, not the whole table. For example, if your product codes are in column D, you’d enter D:D or D2:D100.
3. return_array
The single column (or row) containing the values you want to return. For example, if prices are in column F, you’d enter F:F or F2:F100. Crucially, this column can be anywhere — to the left or right of the lookup column.
A complete basic example:
=XLOOKUP(A2, D:D, F:F)
This looks for the value in A2 within column D, and returns the corresponding value from column F.
The Three Optional Arguments
XLOOKUP has three additional optional arguments that give it a lot of extra power.
4. if_not_found
This replaces the #N/A error with a message of your choice if no match is found. For example:
=XLOOKUP(A2, D:D, F:F, "Not found")
In VLOOKUP you’d need to wrap the whole formula in IFERROR() to achieve the same thing. Here it’s built in.
5. match_mode
This controls how XLOOKUP matches the lookup value:
0— exact match (default, what you’ll use most of the time)-1— exact match or next smaller value (like VLOOKUP approximate match)1— exact match or next larger value2— wildcard match (using * and ? characters)
6. search_mode
This controls the direction of the search:
1— search from first to last (default)-1— search from last to first (useful for finding the most recent entry in a list)2— binary search, ascending order-2— binary search, descending order
For most everyday use you won’t need to specify these — just the first three arguments are enough.
Looking Left: Something VLOOKUP Can’t Do
In VLOOKUP, the search column must always be on the left of the return column. XLOOKUP has no such restriction. If your customer names are in column C and their IDs are in column A, XLOOKUP can search column C and return column A without any workaround.
=XLOOKUP("Smith", C:C, A:A)
With VLOOKUP you’d have to rearrange your data or use a more complicated INDEX/MATCH formula. XLOOKUP handles it directly.
Returning Multiple Columns at Once
XLOOKUP can return more than one column in a single formula. If you want to return both the price and the stock level for a product, simply select both columns as your return_array:
=XLOOKUP(A2, D:D, F:G)
This returns the matching values from both column F and column G, spilling across two adjacent cells. This is called a spill result and only works in Excel 365 and Excel 2021 with dynamic arrays support. Make sure the cells to the right are empty or you’ll get a #SPILL! error.
Using the if_not_found Argument
The if_not_found argument is one of the most practical improvements over VLOOKUP. Instead of the red #N/A error appearing when a value isn’t found, you can display a custom message or a zero:
=XLOOKUP(A2, D:D, F:F, "Price not available")
=XLOOKUP(A2, D:D, F:F, 0)
If you leave this argument blank, XLOOKUP returns #N/A just like VLOOKUP — so it’s worth always filling it in for cleaner spreadsheets.
Using Wildcards
With match_mode set to 2, XLOOKUP supports wildcard characters:
*matches any sequence of characters?matches any single character
For example, to find any product whose name starts with “Widget”:
=XLOOKUP("Widget*", D:D, F:F, "Not found", 2)
XLOOKUP vs VLOOKUP Side by Side
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Can look left | No | Yes |
| Return column method | Column number (fragile) | Direct range reference (robust) |
| Built-in error handling | No (need IFERROR) | Yes (if_not_found argument) |
| Return multiple columns | No | Yes |
| Wildcard support | Limited | Yes (match_mode 2) |
| Search direction | Top to bottom only | Top to bottom or bottom to top |
| Compatibility | All Excel versions | Excel 365 and 2021+ only |
Availability: Which Versions of Excel Support XLOOKUP
XLOOKUP is available in:
- Microsoft 365 (all subscription plans)
- Excel 2021 (standalone purchase)
- Excel for the web
It is not available in Excel 2019, Excel 2016, or earlier versions. If you send a file containing XLOOKUP to someone using an older version of Excel, they’ll see a #NAME? error. If compatibility with older versions matters, stick with VLOOKUP or use INDEX/MATCH instead.
When to Use XLOOKUP vs VLOOKUP
Use XLOOKUP if you’re on a supported version and don’t need to share files with people on older Excel. It’s simpler to write, harder to break, and more capable. Use VLOOKUP if you need maximum compatibility with older versions of Excel, or if you’re working in a shared environment where not everyone has Excel 365 or 2021.
For new spreadsheets where you control the environment, XLOOKUP is the better choice every time.
