Excel XLOOKUP Function

XLOOKUP searches a range for a value and returns a matching result from another range, in any direction you choose. Unlike VLOOKUP, it isn't locked to searching the leftmost column, and it won't break the moment someone inserts a column into your table.

Available in: Excel 365, Excel 2021. Not available in Excel 2019 or earlier.

Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Parameter	Required	Description
`lookup_value`	Yes	The value you're searching for.
`lookup_array`	Yes	The range XLOOKUP searches. It can be a column, a row, or a table column reference — no restriction on position.
`return_array`	Yes	The range to pull a result from. This is a separate range from `lookup_array`, so there's no column counting involved.
`if_not_found`	No	What to return when nothing matches. Defaults to `#N/A` if you leave it out.
`match_mode`	No	`0` for exact match (the default), `-1` for exact match or next smaller item, `1` for exact match or next larger item, `2` for wildcard match.
`search_mode`	No	`1` searches first to last (the default), `-1` searches last to first, `2` and `-2` run a binary search on sorted data.

✓

Skip if_not_found when you actually want to catch and log missing values elsewhere in your workbook. Set it when you'd rather show something readable, like "Not found" or a blank string, directly in the cell.

Basic Example

You're running a product catalog with SKUs in column A and prices in column B, and you need to pull the price for whatever SKU shows up in a customer order.

=XLOOKUP(E2, A2:A500, B2:B500)

// E2      — the SKU you're looking up
// A2:A500 — where XLOOKUP searches
// B2:B500 — where the matching price comes from

XLOOKUP scans column A for the value in E2. Once it finds a match, it returns whatever sits in the same row of column B — no column index number, no counting from the left edge of a table. Just two ranges: where to look, and what to return.

How XLOOKUP Works

Lookup and return ranges are separate

VLOOKUP forces your lookup values and your results into the same table, with a column number linking them. XLOOKUP splits that into two independent ranges. That means the return range doesn't have to sit next to the lookup range at all — it can be in a different sheet, a different column order, anywhere.

It matches exactly by default

This is the single biggest difference from VLOOKUP. Leave match_mode out and XLOOKUP requires an exact match. VLOOKUP defaults to approximate match, which is how so many spreadsheets end up with silently wrong numbers. XLOOKUP got the default right the first time.

It looks in any direction

Point lookup_array at a column to the right of return_array and XLOOKUP happily looks left. There's no directional restriction the way there is with VLOOKUP and HLOOKUP. One function replaces both.

Missing values don't require a wrapper function

=XLOOKUP(A2, products[SKU], products[Price], "Not found")

VLOOKUP needs an outer IFNA to catch a missing value. XLOOKUP takes the fallback as a built-in fourth argument. One formula, no nesting.

It can return an entire row or column at once

Point return_array at multiple columns instead of one, and XLOOKUP spills the whole result across adjacent cells automatically. You don't need to copy the formula sideways for each field.

=XLOOKUP(A2, employees[ID], employees[Department]:employees[Salary])

That single formula spills department and salary into two cells side by side.

Search mode matters on large sorted datasets

search_mode defaults to scanning first to last, which is fine for most tables. On a dataset with tens of thousands of rows that's already sorted, switching to 2 (binary search) can noticeably speed up recalculation. Leave it alone unless you're actually hitting performance problems.

Common Use Cases

Two-way lookup

Pull a value from the intersection of a row and a column — say, unit sales for a specific product in a specific month — by nesting one XLOOKUP inside another:

=XLOOKUP(H2, B4:B20, XLOOKUP(H3, C3:N3, C4:N20))

// H2     — the product name
// B4:B20 — the product column to search
// H3     — the month header
// C3:N3  — the row of month headers
// C4:N20 — the full data grid

The inner XLOOKUP finds the right column for the month. The outer one finds the right row for the product and returns the value where they meet.

Left lookup without restructuring anything

Say your customer ID sits in column D but the customer name you need is in column A. VLOOKUP can't reach backward. XLOOKUP doesn't care:

=XLOOKUP(F2, D2:D300, A2:A300)   // returns the name, even though A sits left of D

Approximate match for commission tiers

Use match_mode -1 to find the largest tier threshold that's still less than or equal to a rep's sales total — this works even if the table isn't sorted, which sets it apart from VLOOKUP's approximate match:

=XLOOKUP(C2, tier_thresholds, tier_rates, "", -1)

// C2               — the rep's total sales
// tier_thresholds  — sales cutoffs for each tier
// tier_rates       — the commission rate for each tier
// ""                — fallback if C2 is below every threshold
// -1                — match exactly, or fall back to the next smaller threshold

Returning multiple fields from one lookup

Set up a small dashboard where typing an employee ID pulls their department, manager, and start date, all from a single formula:

=XLOOKUP(A2, employees[ID], employees[Department:Start Date])

Change the ID once, and every dependent field updates without three separate formulas.

Handling Errors

#N/A shows up when XLOOKUP can't find a match and you haven't set if_not_found. Set the fourth argument instead of wrapping the whole formula in IFNA or IFERROR — it's cleaner and it's built for exactly this:

=XLOOKUP(A2, products[SKU], products[Price], "Not found")
=XLOOKUP(A2, products[SKU], products[Price], "")

#VALUE! shows up when lookup_array and return_array aren't the same size. If your lookup range is 500 rows and your return range is 480, XLOOKUP can't line them up and will tell you so.

⚠

A mismatched range size fails loudly with #VALUE!, so it's easy to catch. A silently wrong approximate match — the kind VLOOKUP produces — is much harder to spot. XLOOKUP's exact-match default is the main reason to prefer it.

Notes & Gotchas

Why does XLOOKUP return #NAME? in my workbook?

XLOOKUP only exists in Excel 365 and Excel 2021. Open that same workbook in Excel 2019 or earlier and every XLOOKUP formula shows #NAME? instead of a value. If you're sharing a file with someone on an older version, stick with VLOOKUP or INDEX/MATCH.

Does XLOOKUP work with dates?

Yes, and it handles them the same way it handles numbers. Store the date as an actual date value rather than text, and both exact match and approximate match work as expected.

What happens with duplicate values in the lookup array?

XLOOKUP returns the first match by default, same as VLOOKUP. Set search_mode to -1 to return the last match instead. Neither setting returns every match — for that, pair XLOOKUP with FILTER.

Why does a wildcard search return #N/A?

Because match_mode defaults to exact match, and a * or ? in your lookup value is treated as a literal character rather than a wildcard. Set match_mode to 2 to turn wildcard matching on:

=XLOOKUP("Sm*", A2:A50, B2:B50, "Not found", 2)

Without that fifth argument, XLOOKUP searches for the literal text "Sm*" and comes up empty. This is one of the most common XLOOKUP mistakes — the formula looks correct, but the match mode silently defaults to the wrong behavior for what you're trying to do.

Can XLOOKUP replace HLOOKUP too?

Yes. Because lookup_array and return_array can each be a row instead of a column, one XLOOKUP formula does the job of both VLOOKUP and HLOOKUP. There's no separate horizontal version to learn.

Related Functions

Function	Use this when...
VLOOKUP	You're on Excel 2019 or earlier, or maintaining an older workbook that already relies on VLOOKUP or HLOOKUP.
INDEX and MATCH	You need a lookup that works in any Excel version, including ones without XLOOKUP.