XLOOKUP vs VLOOKUP: every pattern converted, and the two things nobody mentions

XLOOKUP replaces VLOOKUP, HLOOKUP and most INDEX/MATCH combinations with one function that is shorter to write, safer by default, and does not break when someone inserts a column. The short answer is that you should use it.

The longer answer has two parts that most comparisons skip: there is exactly one thing that can stop you adopting it, and there is a performance problem that appears once you use it at scale. Both are covered below, alongside every VLOOKUP pattern and its XLOOKUP equivalent.

The four things VLOOKUP gets wrong

These are design decisions rather than bugs, and XLOOKUP reverses all four.

It cannot look left

The lookup column must be the first column of the range. If your ID is in column D and the name you want is in column B, VLOOKUP cannot do it — you rearrange the sheet or fall back to INDEX/MATCH.

The column index is a hard-coded number

=VLOOKUP(A2, D:F, 3, FALSE)

That returns column F today. Insert a column anywhere inside D:F and it returns column E instead. No error, no warning — just different numbers in a report that still looks correct. This is the most expensive VLOOKUP failure because nothing signals it.

The default is the dangerous one

Omit the fourth argument and VLOOKUP performs an approximate match:

=VLOOKUP("Widget-B", D:F, 3)      <- no FALSE, so approximate

On unsorted data, approximate match does not mean "close enough". It means VLOOKUP walks the list assuming it is sorted, stops when it passes the value, and returns whatever was before that point. Look up a product code that does not exist and you get a different product's price, returned confidently with no error.

Defaults should fail safely. This one fails silently, and it is the reason experienced Excel users type FALSE automatically.

It reads more than it needs

VLOOKUP(A2, D:F, 3, FALSE) works across the whole D:F block. XLOOKUP reads only the two columns you name. On large sheets this matters, though as the performance section explains, it is not the biggest factor.

The basic conversion

=VLOOKUP(A2, D:F, 3, FALSE)
=XLOOKUP(A2, D:D, F:F)

You name the return column directly instead of counting to it, and exact match is now the default. Two of the four problems are gone in one rewrite.

Looking left

The pattern VLOOKUP cannot do at all. IDs in column D, names in column B:

=INDEX(B:B, MATCH(A2, D:D, 0))     <- the old workaround
=XLOOKUP(A2, D:D, B:B)             <- same result

XLOOKUP has no concept of direction. The lookup array and the return array are independent ranges in any order, and they do not need to be adjacent or even on the same part of the sheet.

Handling misses without hiding real errors

The standard VLOOKUP idiom wraps the whole formula:

=IFERROR(VLOOKUP(A2, D:F, 3, FALSE), "Not found")

This catches far more than intended. IFERROR swallows every error: a #REF! from a deleted column, a #VALUE! from a type mismatch, a #NAME? from a typo. All of them display as "Not found", so a genuinely broken formula is indistinguishable from a record that legitimately is not there.

XLOOKUP's fourth argument fires only on an actual miss:

=XLOOKUP(A2, D:D, F:F, "Not found")

A #REF! still surfaces as #REF!. That is the difference between finding a broken workbook in ten seconds and finding it in an afternoon.

Where the result feeds arithmetic, return a number rather than text:

=XLOOKUP(A2, D:D, F:F, 0)

Match modes

Modes -1 and 1 handle bracket lookups — tax bands, volume discounts, grade boundaries — and improve on VLOOKUP twice over: you choose which direction to round, and the data does not need to be sorted.

=XLOOKUP(B2, $E$2:$E$8, $F$2:$F$8, , -1)   <- commission rate for a sales figure

Note the empty fourth argument, holding the position.

Mode 2 is worth knowing for the opposite reason. VLOOKUP treats * and ? as wildcards whether you wanted that or not, so a product code containing a literal asterisk matches the wrong row. XLOOKUP treats them as ordinary characters unless you ask for mode 2 — another unsafe default quietly removed.

Searching backwards

The sixth argument has no VLOOKUP equivalent at all, and it solves a problem people normally reach for array formulas to handle.

Mode -1 is the most useful and least known feature of the function. On an append-only log, it returns the most recent entry for a key:

=XLOOKUP(A2, D:D, F:F, "none", 0, -1)   <- latest price for this SKU

The VLOOKUP equivalent is a nested INDEX/MATCH/MAX array formula that most people copy from a forum without understanding. Here it is one argument.

The binary modes are a performance option covered below. They have a sharp edge: on data that is not genuinely sorted they return wrong answers rather than errors.

Returning several columns at once

Give XLOOKUP a multi-column return array and it spills the whole row:

=XLOOKUP(A2, D:D, F:H)   <- three columns from one formula

One formula instead of three, and — more importantly — one search instead of three. The cells to the right must be empty or you get #SPILL!.

Two conditions at once

Matching on region and product traditionally meant a concatenated helper column. XLOOKUP does it directly, using the fact that multiplying two arrays of TRUE/FALSE gives 1 only where both are true:

=XLOOKUP(1, (A2:A100="North")*(B2:B100="Widget"), C2:C100, "No match")

The lookup value is 1 because that is what a row satisfying both conditions evaluates to. For "either condition", add instead of multiplying:

=XLOOKUP(1, (A2:A100="North")+(A2:A100="South"), C2:C100)

Keep the ranges the same size and explicit — a whole-column reference here is the slowest thing in this article.

Two-way lookup

Row and column at once, the case that used to need INDEX with two MATCHes:

=INDEX(B2:H50, MATCH(J2, A2:A50, 0), MATCH(K2, B1:H1, 0))
=XLOOKUP(J2, A2:A50, XLOOKUP(K2, B1:H1, B2:H50))

The inner XLOOKUP selects the column; the outer one selects the row from it. This is the one place where XLOOKUP is arguably less elegant than the alternative, since nesting a function inside itself reads oddly at first. It is worth learning because it keeps the whole workbook on one function.

Replacing HLOOKUP

There is no separate horizontal function — point the arrays at rows instead of columns:

=HLOOKUP(A2, 1:5, 4, FALSE)
=XLOOKUP(A2, 1:1, 4:4)

When INDEX/MATCH is still the right answer

INDEX/MATCH is often described as obsolete. It is not, for three specific reasons.

Compatibility. It works in every version of Excel ever shipped, and in every spreadsheet application. This is the deciding factor for any file that leaves your organisation.

Repeated lookups against the same row. If you need eight columns from one matched row, MATCH once into a helper cell and INDEX that position eight times. The row is found once. Eight XLOOKUPs would search eight times — though one XLOOKUP returning eight columns also solves this, so the advantage is narrower than it used to be.

Separating the find from the fetch. Occasionally you want the row number rather than the value, for use in another formula. MATCH gives you that directly.

Outside those cases, XLOOKUP is shorter, safer and easier to audit.

Performance: why lookup-heavy workbooks get slow

This is where most XLOOKUP advice stops, and where the real problems start. The question people ask is whether XLOOKUP is faster than VLOOKUP. It is the wrong question: the difference between the two functions is small next to the difference made by how you use either of them.

Four things dominate, in this order.

Whole-column references

D:D on a modern worksheet is 1,048,576 rows. Across a few thousand formulas, that is billions of cells under consideration. It is the single most common cause of a workbook that takes thirty seconds to recalculate.

=XLOOKUP(A2, $D$2:$D$5000, $F$2:$F$5000, "")

Better, put the source in a table and reference it structurally, so the range tracks the data:

=XLOOKUP(A2, Products[SKU], Products[Price], "")

One lookup per returned column

Three XLOOKUPs on the same key search the same column three times. One spilled lookup searches once:

=XLOOKUP(A2, Products[SKU], Products[[Name]:[Price]])

Linear search on sorted data

If the lookup column is genuinely sorted ascending, search_mode 2 switches to a binary search, which examines a fraction of the rows.

=XLOOKUP(A2, Products[SKU], Products[Price], "", 0, 2)

Only do this where you control the sort. On unsorted data it does not error — it returns wrong values, which is worse than being slow.

Finding the same row repeatedly

This is the real substance behind "INDEX/MATCH is faster than XLOOKUP". The function is not the difference; the technique is. MATCH once into a helper column, then INDEX that stored position for each column you need, and the search happens once per row instead of once per cell.

Past about 100,000 rows

No lookup function recalculates a sheet of that size comfortably on every edit. At that point the answer is to stop joining with formulas: do the join in Power Query, or load both tables into the Data Model and relate them there. Excel is doing set-based work, and the tools built for set-based work are much faster at it.

Measure your own workbook before and after each change rather than trusting any published figure, including the ordering here. Formula count, range size, volatile functions and hardware all move the result.

The one thing that can stop you: version support

XLOOKUP requires Microsoft 365, Excel 2021, Excel 2024, or Excel for the web. It does not exist in Excel 2019, 2016 or earlier.

Open an XLOOKUP workbook in an older version and the formula appears as _xlfn.XLOOKUP(...) returning #NAME?. The formula is preserved rather than destroyed — it works again on a supported version — but the file is unusable in the meantime, and a colleague who saves it in that state can propagate the damage.

What this means in practice:

• Internal workbooks on a managed 365 estate: convert freely.
• Files sent to clients, suppliers, or regulators: stay on INDEX/MATCH. You do not control their Excel version and you will not hear about the breakage.
• Finance and government desktops are the usual holdouts. Check before starting, via File → Account → About Excel.

Google Sheets and LibreOffice

Google Sheets supports XLOOKUP, so a workbook converted for Sheets is fine. LibreOffice Calc added it in version 24.8 — earlier releases will show the same #NAME? failure as old Excel.

Conversion reference

Migrating without breaking a working file

Do not bulk find-and-replace a production workbook. The order that works:

1. Confirm the Excel version of everyone who opens the file. If any of them is on 2019 or earlier, stop — this is the only genuine blocker.
2. Copy the workbook and write down the current totals: every summary figure the file shows right now.
3. Convert one block at a time, starting with the formulas whose hard-coded column index is most at risk from an inserted column.
4. Compare the totals after each block. A mismatch is nearly always an approximate-match VLOOKUP that was doing something nobody documented.
5. Replace the IFERROR wrappers last, and read what they were hiding.

Step five surprises people. Converting frequently reveals errors that were present all along and suppressed by a blanket IFERROR. That is a finding, not a regression — those cells were already wrong, and now you can see them.

Common questions

Is XLOOKUP better than VLOOKUP?

Yes, on almost every measure: it looks in both directions, defaults to exact match, survives inserted columns, handles misses without hiding real errors, and searches backwards. The single reason not to use it is that the people opening your file may be on Excel 2019 or older.

What is the disadvantage of XLOOKUP?

Version support is the real one — Microsoft 365, Excel 2021 or later only. Beyond that: it has six arguments rather than four, so there is more to learn; two-way lookups require nesting it inside itself, which reads awkwardly; and because it is easy to point at whole columns, it makes it easy to write a slow workbook.

Does XLOOKUP run faster than VLOOKUP?

Usually a little, because it reads two named arrays rather than a block spanning every column between the key and the result. But the function choice is a small effect. Range size, the number of formulas, and whether you search once or repeatedly all matter far more — see the performance section above.

Should I still learn VLOOKUP?

Yes, for reading. Decades of existing spreadsheets use it, and you will inherit them. Write new work in XLOOKUP; be able to read VLOOKUP.

Can XLOOKUP return multiple matches?

No — it returns one match, the first or the last depending on search_mode. For every matching row, use FILTER instead, which is available in the same Excel versions.

Excel's function availability and behaviour change between releases. Check your version under File → Account → About Excel, and test any conversion against a copy before applying it to a workbook people depend on.