The Excel XLOOKUP function, explained interactively.
XLOOKUP is the modern successor to VLOOKUP. It searches a single column for a value and returns the matching cell from any other column — including one to the left of where you searched. Defaults to exact match, has a built-in “not found” fallback, and reads naturally left-to-right. Change the parameters below and watch the formula resolve in real time.
How to use XLOOKUP
Pick a value, pick which column to search, pick which column to return — XLOOKUP handles the rest. Try lookup = “Carol” with lookup_array = Name (column B) to see the reverse lookup VLOOKUP cannot do.
"E003" or a name like "Carol".lookup_array — XLOOKUP’s headline feature.if_not_found (custom fallback), match_mode (0 / -1 / 1 / 2), search_mode (1 / -1 / 2 / -2). Omit unless you need them — see the Examples.| A | B | C | D | |
|---|---|---|---|---|
| 1 | ID | Name | Dept | Salary |
| 2 | E001 | Alice | Sales | 72000 |
| 3 | E002 | Bob | Eng | 95000 |
| 4 | E003 | Carol | Design | 88000 |
| 5 | E004 | Dan | Eng | 102000 |
| 6 | E005 | Eve | Sales | 67000 |
| 7 | ||||
| 8 | XLOOKUP result | Design |
XLOOKUP syntax and arguments
Six arguments — three required, three optional. The optional tail is where XLOOKUP earns its keep: if_not_found, match_mode, search_mode.
lookup_array. Can be a literal, a number, or a cell reference. Matching is case-insensitive by default. Supports wildcards only when match_mode = 2.table_array, this doesn’t have to include the return column.lookup_array. Can sit anywhere in the sheet — including to the left— which is XLOOKUP’s single biggest advantage over VLOOKUP.#N/A. Pass a string (e.g. "Not in catalog") to replace the ugly error with a user-friendly message — no IFERROR wrapper needed.0— exact match (default)-1— exact, or next smaller item1— exact, or next larger item2— wildcard match (*and?)
-1 and 1 don’t require the data to be sorted.1— first to last (default)-1— last to first (useful for “most recent” matches)2— binary search, sorted ascending (faster on huge ranges)-2— binary search, sorted descending
XLOOKUP examples
Four patterns that cover 90% of real-world XLOOKUP use.
Example 1: XLOOKUP for a basic value lookup
The canonical XLOOKUP — the drop-in upgrade from a VLOOKUP exact match. No FALSE required; exact match is the default.
Searches column A for E003, returns the matching value from column C (Dept: Design). Note how much shorter the formula is than the equivalent =VLOOKUP("E003", A2:D6, 3, FALSE).
Example 2: XLOOKUP returning a value to the left
VLOOKUP’s hardest limitation — you can’t look up a name and return its ID, because the ID column sits to the left of the name column. XLOOKUP doesn’t care.
Searches the Name column (B) for Carol, returns the matching ID from column A (E003). In VLOOKUP you’d need INDEX/MATCH to pull this off.
Example 3: XLOOKUP with if_not_found — no IFERROR needed
VLOOKUP forces you to wrap every call in IFERROR to get a useful “not found” message. XLOOKUP builds the fallback in as a fourth argument.
If A2 has no match in the Catalog’s ID column, the cell reads "Not in catalog" instead of #N/A. Cleaner than =IFERROR(VLOOKUP(…), "Not in catalog").
Example 4: XLOOKUP with wildcards (match_mode 2)
For partial matching on text — product codes, names, SKUs. Unlike VLOOKUP wildcards (which require FALSE), XLOOKUP needs explicit match_mode = 2.
Finds the first product in column B whose name contains “Stand” and returns its price. Note the two commas before 2 — we’re skipping the if_not_found slot since positional arguments require placeholders.
Deep dive: XLOOKUP with multiple criteria (three patterns) →
Common XLOOKUP errors and fixes
Four failure modes, each with what to check and how to recover.
XLOOKUP returns #N/A
Cause: no cell in lookup_array matches lookup_value.
Check for hidden whitespace (TRIM), mismatched types (text vs number), or a wrong range. Easiest fix: pass a fallback as the 4th argument — =XLOOKUP(…, …, …, "Not found").
XLOOKUP returns #VALUE!
Cause: lookup_array and return_array are different sizes or orientations.
Both ranges must be identical in length and direction (vertical vs horizontal). If lookup_array is A2:A100, return_array must be 99 cells tall too.
XLOOKUP returns #NAME?
Cause: the version of Excel or Google Sheets doesn’t support XLOOKUP.
XLOOKUP requires Microsoft 365, Excel 2021+, Excel for the web, or Google Sheets. In older Excel (2019 and earlier) it isn’t available — fall back to INDEX/MATCH or VLOOKUP.
XLOOKUP returns #SPILL!
Cause: return_array covers multiple columns and XLOOKUP needs room to spill the result, but the cells to the right are occupied.
Clear the spill range, or narrow return_array to a single column if you only want one value back.
XLOOKUP vs VLOOKUP, INDEX+MATCH & HLOOKUP
Four lookup patterns. XLOOKUP supersedes three of them on modern Excel — but the older ones still live in legacy workbooks.
| Function | Direction | Looks left? | Default match | Availability |
|---|---|---|---|---|
| XLOOKUP | Either | Yes | Exact (modern) | Microsoft 365, Excel 2021+, Sheets |
| VLOOKUP | Vertical | No | Approximate (legacy) | Everywhere |
| HLOOKUP | Horizontal | — | Approximate | Everywhere |
| INDEX + MATCH | Either | Yes | Exact (explicit) | Everywhere |
Pick XLOOKUP on any modern Excel or Google Sheets; it’s simpler than INDEX/MATCH and strictly more flexible than VLOOKUP. Keep VLOOKUP in your toolkit only for legacy file compatibility and interview questions.
XLOOKUP frequently asked questions
6.01What’s the difference between XLOOKUP and VLOOKUP?▸
XLOOKUP defaults to exact match (VLOOKUP defaults to approximate), can return a value to the left of the lookup column, has a built-in if_not_found argument, and takes two separate column ranges instead of VLOOKUP’s numeric col_index_num. XLOOKUP requires Microsoft 365 or Excel 2021+; VLOOKUP works in every Excel version.
6.02Can XLOOKUP look to the left?▸
Yes — this is XLOOKUP’s headline feature. Pass any column as lookup_array and any other column (left or right of it) as return_array. VLOOKUP cannot do this without INDEX/MATCH gymnastics.
6.03Is XLOOKUP available in Google Sheets?▸
Yes, XLOOKUP has been supported in Google Sheets since 2022 with identical syntax to Excel. All six arguments behave the same way.
6.04How do I make XLOOKUP return a custom message instead of #N/A?▸
Pass the 4th argument: =XLOOKUP(lookup, lookup_array, return_array, "Not found"). This replaces the need for an IFERROR wrapper that VLOOKUP users commonly rely on. See if_not_found in detail for five fallback patterns and the migration from IFERROR(VLOOKUP(…)).
6.05What does match_mode 2 do in XLOOKUP?▸
match_mode = 2 enables wildcard matching. * matches any sequence of characters, ? matches a single character. Only applies to text lookup_array — numeric matches ignore wildcards.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.