XLOOKUP was introduced as a more flexible replacement for VLOOKUP, HLOOKUP, and INDEX MATCH combined. This tutorial starts from scratch, covering what problem XLOOKUP solves, its full syntax, and a worked example using a product table.
By the end, you'll be able to write your own XLOOKUP formulas and know when Turbo Excel VLookup is a faster route for matching a whole dataset instead of one cell.
Unlike VLOOKUP, which searches a combined range and counts columns, XLOOKUP takes the lookup column and return column as two entirely separate arguments. This removes the leftward-search restriction and the need to count column positions.
Only the first three arguments are required for a basic lookup; the rest are optional and cover fallback values, match precision, and search direction.
Using this product table, to find the Price for SKU-2203:
| SKU | Product Name | Category | Price |
|---|---|---|---|
| SKU-2201 | Wireless Mouse | Electronics | 699 |
| SKU-2202 | Desk Lamp | Home | 899 |
| SKU-2203 | Notebook Pack | Stationery | 150 |
The optional fourth argument, if_not_found, replaces the need for a separate IFERROR wrapper:
XLOOKUP is still a per-cell formula that needs to be copied down a column to match a full list. Turbo Excel VLookup applies the same logic across an entire dataset at once, which is usually faster once more than a handful of matches are needed.
Just like VLOOKUP, XLOOKUP will fail to match values that look identical but are stored as different data types - text versus number is the most common culprit.
If a lookup that should clearly match is instead falling through to if_not_found, converting the lookup value with VALUE() or TEXT() to match the source column's format usually resolves it.
A short comment or adjacent label explaining what an XLOOKUP formula is matching and why makes a shared workbook far easier to maintain, especially once several lookups start feeding into each other across different sheets.
XLOOKUP's optional fifth argument, match_mode, defaults to an exact match, but it also supports approximate matches - either the next smaller or next larger value when no exact match exists - by passing -1 or 1 instead of the default 0.
This is useful for scenarios like matching a score against grade thresholds, where an exact match won't always exist but the closest applicable bracket still needs to be found.
What does XLOOKUP replace?
It can replace VLOOKUP, HLOOKUP, and most uses of INDEX MATCH in a single, more flexible function.
How many arguments does XLOOKUP support in total?
Six, though only the first three - lookup_value, lookup_array, and return_array - are required.
Does XLOOKUP require the lookup and return columns to be adjacent?
No, they're selected as separate ranges and can be positioned anywhere relative to each other.
How does XLOOKUP handle errors differently than VLOOKUP?
It has a built-in if_not_found argument, avoiding the need for a separate IFERROR wrapper.
Can XLOOKUP search from the bottom of a list upward?
Yes, using the optional search_mode argument, XLOOKUP can search in either direction.
What's a faster option than copying XLOOKUP down a column?
Turbo Excel VLookup matches an entire dataset in one action instead of one formula per row.
Why would a text-formatted number fail to match a real number?
Excel treats text and numeric values as different data types, so "101" stored as text won't match 101 stored as a number even though they display the same.
Does locking a range with F4 change how the formula calculates?
No, it only fixes the range so it doesn't shift when the formula is copied - the calculation itself works the same either way.
What's a fast way to check what a formula is actually returning?
Selecting part of the formula in the formula bar and pressing F9 shows its calculated value directly, without changing the formula.
Can trailing spaces in a cell cause a lookup to fail?
Yes, an extra space at the end of a value is invisible but prevents an exact match; TRIM() around the lookup value often fixes this.
Ready to stop fighting #N/A errors and match your spreadsheets visually?