XLOOKUP
The modern replacement for VLOOKUP. Cleaner syntax, safer defaults, works in any direction, has built-in error handling. If you're on Excel 365 or 2021+, this should be your default choice for every new lookup.
Searches for a value in one range and returns a corresponding value from another range. Handles left/right/up/down lookups and returns your chosen value when nothing matches.
What XLOOKUP does
XLOOKUP is Microsoft's official replacement for VLOOKUP, HLOOKUP, and even some uses of INDEX/MATCH. It arrived in 2020 to fix nearly every complaint people had about VLOOKUP: rigid left-to-right direction, silent errors on approximate match, ugly #N/A output, and cryptic column-index counting.
With XLOOKUP you pass the lookup array and return array as separate ranges — they don't have to be adjacent, and either can be to the left or right of the other. You can specify what to return when nothing matches (goodbye IFERROR wrappers). The default is exact match (goodbye silent bugs). And it can return multiple values as a spilled array (goodbye INDEX/MATCH array formulas).
💡 The one reason to still learn VLOOKUP
XLOOKUP is only available in Excel 365, Excel 2021, and Excel Online. If your workbook needs to open in Excel 2019 or earlier — or on a Mac that hasn't been updated — you'll still need VLOOKUP. Learn XLOOKUP for new work, but keep VLOOKUP knowledge for the workbooks you inherit.
Syntax breakdown
XLOOKUP takes up to six arguments. Only the first three are required.
The value you want to find. Can be a number, text, date, cell reference, or expression. Works with wildcards when match_mode is set to 2.
The range or array to search. Must be a single column or single row. Can be to the left or right of the return_array — no direction restriction.
The range or array containing the value(s) to return. Must be the same size as lookup_array. If it has multiple columns, XLOOKUP returns the entire row (or multiple rows if there are multiple matches with dynamic arrays).
What to return when no match is found. If omitted, XLOOKUP returns #N/A. Set this to "Not found", 0, or any value you want — the equivalent of wrapping in IFERROR, but cleaner.
0 exact match (default), -1 exact match or next smaller item, 1 exact match or next larger item, 2 wildcard match. Default of 0 means safe by default — no more silent approximate-match bugs.
1 search first to last (default), -1 search last to first, 2 binary search ascending, -2 binary search descending. Use -1 when you want the last matching row instead of the first (common for "get most recent" scenarios).
5 real-world examples
Find a product's price by ID
You have product IDs in A2:A100 and prices in B2:B100. In E2 you have a product ID and want F2 to show the price.
Notice how much cleaner this is than VLOOKUP. No column counting, no FALSE argument needed — exact match is the safe default.
Return a fallback when the value doesn't exist
Same setup as Example 1, but instead of #N/A when the product doesn't exist, show "Not in catalog".
No IFERROR wrapping needed. This is one of XLOOKUP's biggest ergonomic wins — the fallback is built into the function.
Return a value from a column left of the lookup column
You have customer IDs in column A and customer names in column B. Given a customer name, you want the ID.
XLOOKUP doesn't care about direction. Look up in B, return from A. VLOOKUP would need a helper column or you'd have to switch to INDEX/MATCH.
Search from bottom up to find the last occurrence
You have a transaction log where the same customer appears multiple times, one row per transaction. You want the amount from that customer's LAST transaction (most recent, assuming rows are chronological).
The -1 in search_mode reverses the search direction. VLOOKUP would return the first match; XLOOKUP gives you both options.
Get multiple columns at once from a matching row
You have a customer database in A2:E100 with ID, Name, Email, Phone, City. Given a customer ID, you want all four other fields to spill into adjacent cells.
This uses XLOOKUP's dynamic array output. The return_array is a 4-column range, so the result spills across 4 cells. Impossible with plain VLOOKUP — you'd need one formula per column.
Common errors and how to fix them
Value truly not in the lookup_array
The most common XLOOKUP error. The value you're searching for genuinely doesn't exist in the lookup range, or has invisible differences (trailing spaces, text vs number).
lookup_array and return_array have different sizes
The lookup_array is 100 rows but return_array is 99 rows. They must match exactly.
Return range needs multiple cells but they're blocked
You wrote =XLOOKUP(E2, A2:A100, B2:E100) which returns 4 cells, but the cells next to your formula aren't empty.
XLOOKUP not available in this Excel version
You're on Excel 2019 or earlier, which doesn't have XLOOKUP. The formula shows #NAME? because Excel doesn't recognize the function.
For more error diagnosis, see our Lookup Errors guide.
XLOOKUP vs VLOOKUP — the full comparison
Version compatibility
Related functions
📥 Download the practice workbook
All 5 examples above in a working .xlsx file with sample data. Compare XLOOKUP and VLOOKUP side by side.
Frequently asked questions
Should I always use XLOOKUP over VLOOKUP?
In new work, yes — if you're on Excel 365 or 2021+. The syntax is cleaner, defaults are safer, and it handles more scenarios. The only reason to use VLOOKUP in new work is if the workbook must open in Excel 2019 or earlier.
Is XLOOKUP slower than VLOOKUP?
On modern Excel, no noticeable difference for typical workbook sizes. Both are highly optimized. For very large ranges (100K+ rows), consider binary search mode (search_mode = 2) with sorted data for maximum speed.
Can XLOOKUP do a two-way lookup?
Yes, by nesting one XLOOKUP inside another: =XLOOKUP(row_key, row_col, XLOOKUP(col_key, col_headers, data_range)). The inner XLOOKUP returns a column; the outer picks the value in that column.
Does XLOOKUP work in Google Sheets?
Yes — Google added XLOOKUP in 2022 with identical syntax. Cross-compatible for most use cases.
What's the difference between if_not_found and IFERROR?
if_not_found only catches the "not found" case (equivalent to #N/A). IFERROR catches ALL errors, including #VALUE!, #REF!, etc. Use if_not_found for the specific case of "no match", and wrap in IFERROR if you also want to catch structural errors.
Can XLOOKUP handle multiple criteria?
Yes — concatenate criteria in both the lookup value and lookup array: =XLOOKUP(E2 & F2, A2:A100 & B2:B100, C2:C100). The concatenation combined with array evaluation gives you multi-criteria lookup in one formula.
Why does XLOOKUP return 0 instead of "" for blank cells?
XLOOKUP treats blanks in the return array as 0. To return blank instead, wrap in IF: =IF(XLOOKUP(...)=0, "", XLOOKUP(...)). Or use LET to avoid calling XLOOKUP twice: =LET(x, XLOOKUP(...), IF(x=0, "", x)).
Convert legacy VLOOKUPs to XLOOKUP.
Point the Add-in at any VLOOKUP formula — get the equivalent XLOOKUP with safer defaults and cleaner syntax, ready to paste.
Get the Add-in →