XLOOKUP in Excel — The Complete Guide with Examples (2026)

XLOOKUP is the function that finally fixes VLOOKUP. It looks left or right, defaults to exact match, never breaks when columns move, handles “not found” gracefully, and returns whole rows of data in one formula. In 30 minutes you’ll master all six arguments — from a basic lookup to approximate matches, wildcards, reverse search, and two-way lookups — with an interactive builder to try it live. Requires Microsoft 365 or Excel 2021/2024.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

What is XLOOKUP?

XLOOKUP is Excel’s modern lookup function, introduced in Microsoft 365 to replace VLOOKUP and HLOOKUP. Its job is simple: find a value in one range, and return the value sitting in the same position of another range. If you have a product code and want its price, a customer ID and want their name, an employee and want their department — XLOOKUP is the tool.

What makes it special is everything it fixes. VLOOKUP, the function it replaces, has three notorious flaws: it can only look to the right of the lookup column, it defaults to approximate match (silently returning wrong answers when you forget the final FALSE), and it references the return column by a hard-coded number that breaks the moment someone inserts a column. XLOOKUP eliminates all three. It takes the lookup column and the return column as two independent arguments, so the return column can be anywhere; it defaults to exact match; and because it points directly at the return range, inserting columns never breaks it.

XLOOKUP also does more in a single formula. It can return an entire row of columns at once (no more three stacked VLOOKUPs), give a friendly “Not found” message instead of #N/A, search from the bottom up to find the most recent match, and perform approximate or wildcard matching when you need it. Two of these six arguments are all most people ever use; the other four are there when the job gets interesting.

The one catch is availability: XLOOKUP exists only in Microsoft 365 and the perpetual Excel 2021 and 2024 releases. If you or your collaborators are on Excel 2019 or earlier, the formula returns #NAME? and you should reach for INDEX/MATCH instead. For everyone on a current version, though, XLOOKUP is the lookup function to learn — and once you do, you rarely write a VLOOKUP again.

The Syntax — All Six Arguments

XLOOKUP takes three required arguments and three optional ones:

The genius of the design is in the defaults. The three optional arguments are set so that the common case — an exact-match lookup, searching top to bottom, where a missing value should error visibly — needs none of them. You write three arguments and get correct behaviour, where VLOOKUP forced you to remember a fourth (FALSE) or silently return wrong answers. You only reach for arguments four, five, and six when the job genuinely calls for graceful not-found handling, approximate or wildcard matching, or bottom-up search. This “simple by default, powerful when needed” structure is why XLOOKUP is both easier for beginners and more capable for experts than the function it replaces.

One more subtlety worth internalising early: lookup_array and return_array must be the same shape — the same number of rows for a vertical lookup, or the same number of columns for a horizontal one. They do not need to be adjacent, the same width, or in any particular order on the sheet; XLOOKUP simply matches position for position between the two ranges. Getting comfortable with that mental model — “find the position in array A, return that position from array B” — is the key that makes every advanced trick below feel obvious rather than magic.

1.Step 1 — A Basic XLOOKUP

The everyday lookup uses only the three required arguments. Say product codes are in A2:A100 and prices in C2:C100. To find the price of code “WID-01”:

Read it plainly: find WID-01 in A2:A100, and return whatever sits in the same row of C2:C100. No column-counting, no FALSE at the end to force exact match — exact is the default. In practice you would replace the hard-coded “WID-01” with a cell reference like E2 so the lookup follows whatever the user types.

2.Step 2 — Look Up to the Left

This is the trick VLOOKUP could never do. Because the lookup array and return array are separate arguments, the return column can sit anywhere — including to the left of the lookup column. Suppose names are in column A and IDs in column B, and you have an ID and want the name:

Here the lookup array (B) is to the right of the return array (A), and XLOOKUP handles it without complaint. With VLOOKUP this required either rearranging your columns or a clumsy INDEX/MATCH. XLOOKUP makes left lookups a non-event — you simply name the two columns and the direction is irrelevant.

3.Step 3 — Handle “Not Found” Cleanly

When the lookup value does not exist, XLOOKUP returns #N/A by default. The fourth argument, if_not_found, lets you replace that with something useful:

You can return text (“Not found”, “Check code”), a number (0), or a blank string (""). This is cleaner than the old habit of wrapping everything in IFERROR, because IFERROR swallows every error — including a genuine typo in your formula that you would want to see. if_not_found catches only the one specific case where the value is absent, leaving real errors visible.

4.Step 4 — Return Multiple Columns at Once

Need several pieces of data for one lookup — say price, stock, and supplier? Point the return array at multiple columns and XLOOKUP spills all of them:

One formula, three columns returned across the adjacent cells. This replaces three separate VLOOKUPs and stays in sync automatically. Because it spills, make sure the cells to the right are empty — if something blocks the spill range you will get a #SPILL! error. This dynamic-array behaviour is one of XLOOKUP’s most underused superpowers for building record cards and detail panels.

5.Step 5 — Approximate and Wildcard Match

The fifth argument, match_mode, unlocks non-exact lookups. For tiered data — tax bands, commission rates, grade boundaries — use -1 to find the next-smaller value when there is no exact match:

(Note the empty fourth argument — the two commas — to skip if_not_found.) An income of £42,000 against bracket thresholds of 0, 12,570, 50,270 returns the 12,570 band’s rate, exactly as a tax calculation should. Use 1 instead for the next-larger value.

Set match_mode to 2 for wildcard matching, where * means any characters and ? means one character:

6.Step 6 — Search from Last to First

When a value appears more than once, the default returns the first match. The sixth argument, search_mode, set to -1, searches from the bottom up and returns the last match instead:

In a transaction log sorted oldest to newest, this returns the most recent price, status, or entry for an item — a genuinely hard task with VLOOKUP. It is the cleanest way to answer “what is the latest value for X?” without sorting or helper columns. (The empty arguments skip if_not_found and set match_mode to exact.)

7.Step 7 — A Two-Way Lookup

To find a value at the intersection of a row and a column — like sales for a specific product in a specific month — nest one XLOOKUP inside another:

The inner XLOOKUP matches the row label (E2) and returns that entire row of the grid; the outer XLOOKUP then matches the column header (F2) and picks the right cell from that row. This single, readable formula replaces the older INDEX(data,MATCH(...),MATCH(...)) pattern — and because each XLOOKUP names what it is searching for, it is far easier to audit six months later.

8.Step 8 — Replacing VLOOKUP and HLOOKUP

Here is the direct translation for migrating old formulas:

XLOOKUP also replaces HLOOKUP for horizontal lookups — just give it a row as the lookup array and another row as the return array; the function does not care about orientation. Once your collaborators are all on Microsoft 365 or Excel 2021/2024, there is little reason to write a new VLOOKUP or HLOOKUP again.

Try It: Interactive XLOOKUP Builder

Pick a lookup value and a column to return, set the match and search modes, and watch the formula assemble itself and run against the sample table — the matched row highlights in green and the result appears below, exactly as XLOOKUP behaves in Excel. Try a code that does not exist to see the if_not_found text, or set search mode to last-to-first on a duplicated code.

Advanced XLOOKUP Techniques

Match on two or more criteria

XLOOKUP has no native multi-criteria mode, but you can build one by concatenating the criteria arrays with & inside the function. To find a price by both SKU and warehouse:

The lookup value becomes the joined key (e.g. “WID-01London”) and the lookup array becomes the two columns joined the same way. This is one of XLOOKUP’s most powerful tricks: it turns a function that officially supports one criterion into a clean multi-criteria lookup with no helper column. In Microsoft 365 the concatenation evaluates as a dynamic array, so it just works; on slower machines or very large tables, a dedicated helper column may calculate faster.

Combine XLOOKUP with SUM for a lookup-and-aggregate

Because XLOOKUP can return a whole range, you can hand that range straight to another function. =SUM(XLOOKUP(E2,Months,Q1:Q4)) looks up a row and sums it in one step, and dynamic-array spilling means you can even return a block and operate on all of it at once. This composability — one function feeding another — is where XLOOKUP pulls decisively ahead of VLOOKUP, which can only ever hand back a single value.

Use cell references, not hard-coded text

Every example here uses a literal like “WID-01” for clarity, but in real workbooks you should point lookup_value at an input cell (E2) so the result updates as the user types or selects. Pair that input cell with Data Validation drop-down list drawn from the lookup column, and you have a tidy, error-proof lookup interface: pick from the list, see the answer.

When INDEX/MATCH is still the right call

If your file must open in Excel 2019 or earlier, XLOOKUP is unavailable and INDEX(return,MATCH(value,lookup,0)) is the portable equivalent — it does left lookups and exact match, and it works in every version back to Excel 2007. Keep INDEX/MATCH in your toolkit for shared files where you cannot guarantee everyone is on a current Excel. For your own modern workbooks, XLOOKUP’s readability wins.

Common Errors & Fixes

#NAME? error

Your Excel version does not support XLOOKUP (it needs Microsoft 365 or Excel 2021/2024). Use INDEX/MATCH instead, or upgrade. This also happens to collaborators opening your file on an older version.

#N/A error

The lookup value genuinely is not in the lookup array — often a type mismatch (the number 100 vs the text “100”) or a trailing space. Add an if_not_found argument for a clean message, and use TRIM to clean stray spaces.

#SPILL! error

You returned multiple columns (Step 4) but a cell in the spill range is occupied. Clear the cells to the right of the formula so the result has room to spill.

#VALUE! error

The lookup_array and return_array are different sizes. They must have the same number of rows (for a vertical lookup) or columns (for a horizontal one). Re-select both ranges to match.