XLOOKUP vs. VLOOKUP vs. INDEX-MATCH: Which Should You Use?

Pick XLOOKUP when every person who opens the workbook has it, and INDEX-MATCH when someone might not; in the XLOOKUP vs VLOOKUP choice, compatibility decides more than features do. XLOOKUP does what VLOOKUP does with fewer ways to go wrong. INDEX-MATCH, a pairing of two older functions, does nearly as much and runs in every version of Excel. VLOOKUP still works, but its defaults can hand you a wrong answer without any warning. That gives three rules:

1. Use XLOOKUP where everyone has it
2. Use INDEX-MATCH when the file must open in older Excel
3. Keep VLOOKUP for legacy sheets, always with an exact match

Lookups are the formula layer of spreadsheet automation: they pull a value from one table into another so nobody has to retype it.

Prefer to watch? The video covers the same ground as this article.

VideoXLOOKUP vs VLOOKUP vs INDEX‑MATCH: Which Should You Use?WiserHours

What each lookup does, shown on one small table

All three functions do the same job: they find a key, such as a product code, in one column and return a value from the same row of another column. They differ in how you tell them which column to return, and that difference drives almost everything else in this comparison.

Take a three-row price list, with a code typed into cell E2 by whoever needs a price:

With C-200 in E2, each function answers two questions, the price and the product name:

VLOOKUP searches the first column of the range you give it and returns a column you pick by number, counting from 1 at the left edge of that range; FALSE at the end asks for an exact match. INDEX-MATCH splits the job in two. MATCH reports where the code sits in its column (the chair’s code is second in the list), and INDEX returns whatever sits in that position in another column; the 0 asks MATCH for an exact match. XLOOKUP takes a column to search and a column to return, and matches exactly unless you tell it otherwise, as Microsoft’s guide to all three explains.¹

1. VLOOKUP: searches the first column of its range and returns a column to the right, picked by a counted column number
2. INDEX-MATCH: MATCH finds the row position, then INDEX returns the value from any column you point it at, left or right
3. XLOOKUP: searches one column and returns another on either side, and matches exactly unless told otherwise

A useful habit with any of them: read the formula aloud as a sentence. “Find E2 among the codes and give me the price” is what the XLOOKUP says; the VLOOKUP says “find E2 in the first column and give me whatever is in column 2.” The first sentence names the column you want. The second counts to it.

XLOOKUP vs VLOOKUP, with INDEX-MATCH, point by point

When Microsoft launched XLOOKUP in 2019, its Excel team called it the successor to VLOOKUP and strongly recommended it, listing six VLOOKUP limitations it removes, from the approximate-match default to breaking when columns are inserted.² The table sets the three side by side.

Most rows follow from the counting shown above: a column picked by position breaks when the block of cells changes, while a column named directly can sit on either side and survive a rearranged sheet. Check the final row first: if it rules XLOOKUP out for your colleagues, nothing above it matters. Speed gets its own section below.

Two defaults that return wrong answers without warning

VLOOKUP’s two worst habits are silent: it assumes an approximate match unless you say otherwise, and it finds the return column by counting. Both produce a plausible number rather than an error, so they survive unnoticed.

Start with the default. Leave off VLOOKUP’s last argument and it assumes the first column is sorted and returns the closest match at or below your key; if the column is not sorted, Microsoft warns, the result “might be something you don’t expect”.⁶ MATCH has the same default: left blank, it assumes an ascending sort and returns the largest value less than or equal to the key.⁷

Myth

If VLOOKUP returns a number, it found the row you asked for.

Fact

Without FALSE at the end, VLOOKUP runs an approximate match and can return a neighboring row's value from unsorted data, with no error to warn you.

In the price list, the codes are not in alphabetical order. A VLOOKUP written without FALSE may stop at a neighboring row and return that row’s price, and nothing on screen says so. Approximate match does have a proper use, such as finding which tax band or discount tier a value falls into, but only on a column you have sorted on purpose.

The second habit is the column number. Say a colleague inserts a Color column between Code and Price, inside the lookup range. The column number in the VLOOKUP does not change with it, so the formula now returns each item’s color instead of its price. Microsoft’s post puts it plainly: after inserting or deleting a column, you have to change the number inside the VLOOKUP by hand.² INDEX-MATCH and XLOOKUP name the price column as a range of its own, and a range reference moves with its column when others are added around it.

Missing values and lookups on more than one condition

When a key is not in the list, all three functions return #N/A by default; XLOOKUP alone lets you name a replacement inside the formula. For lookups on two or more conditions, Google’s help suggests a helper column for VLOOKUP, while INDEX-MATCH and XLOOKUP can test several columns at once.

A missing key is often news you need, such as an unpriced product. Google’s help shows the usual fix for the older functions, wrapping the lookup in IFNA to show a message such as “NOT FOUND” instead of the error.⁸ XLOOKUP’s fourth argument, if_not_found, does the same without a wrapper, though Exceljet, a long-running Excel tutorial site, warns against filling it with an empty string, which hides every missing value, including the ones you would want to see.⁴ Show a word that stands out, so a blank cell never passes for a found value.

Multiple conditions come up whenever one column is not unique: the price of a hoodie depends on its size and color, not just its name. VLOOKUP cannot take two keys, so Google’s help joins the columns with an ampersand into a helper column and looks up the joined text.⁸ XLOOKUP and INDEX-MATCH can instead multiply one true-or-false test per column and look for the row where every test is true, as in =XLOOKUP(1, (B5:B15=H5)*(C5:C15=H6), E5:E15).⁴⁵ The helper column is the more readable choice when colleagues will maintain the file.

Is XLOOKUP slower? What one speed test found

On large sheets, how the lookup searches matters more than which function you choose. Microsoft’s Excel performance guidance explains that an exact match reads down the column until it finds the key, so the further down the key sits, the longer it takes, while an approximate match on sorted data works like a binary search and barely slows as the list grows.⁹

The study

Limited evidence

Timing 100,000 lookups on one laptop (Professor Excel, 2021)

A workbook of 100,000 XLOOKUP formulas took about 40 percent longer to calculate than the same lookups written with VLOOKUP, and INDEX-MATCH ran at about VLOOKUP’s pace. Switching XLOOKUP to its binary search mode on sorted data cut its time by about two-thirds, which made it faster than an exact-match VLOOKUP. Even the standard XLOOKUP, the slowest of the three, took roughly half a second for all 100,000 formulas.¹⁰

XLOOKUP was slower in this test yet still fast. It ran on one machine and an Excel build Microsoft has updated many times since, so treat its ratios as a snapshot. Ablebits, which sells Excel add-ins, says the opposite, that XLOOKUP is generally faster than INDEX-MATCH for simple lookups, but publishes no test.⁵

For a sheet of a few thousand rows, pick for clarity and compatibility and let speed look after itself. Microsoft’s guidance adds that VLOOKUP is slightly faster than INDEX-MATCH, but that INDEX-MATCH often saves more time overall, because one MATCH result can be stored in a cell and reused by several INDEX formulas. If a workbook does crawl, Microsoft’s advice is to keep lookup ranges no bigger than the data, using tables rather than whole columns, and to sort the data where you can so that a fast approximate or binary search becomes safe.⁹

Who can open the file: versions, languages and separators

XLOOKUP works only where the software knows it: Microsoft’s help page states that it is not available in Excel 2016 or Excel 2019, even though people using those versions may receive workbooks that contain it.¹¹ Google Sheets began rolling out XLOOKUP in August 2022.¹² VLOOKUP and INDEX-MATCH, by contrast, work in every version of both.

When it does not recognize a function name, Excel shows the #NAME? error.¹³ If a shared XLOOKUP turns into #NAME? on a colleague’s machine, check which version of Excel they have. Before you standardize on XLOOKUP, ask who else opens the file, such as a client on an older license.

Language is the second trap. Function names are translated in many language versions: German Excel calls XLOOKUP XVERWEIS and VLOOKUP SVERWEIS, and writes their arguments with semicolons rather than commas.¹⁴ Microsoft’s error guide notes that arguments are separated by a comma or a semicolon depending on your location settings.¹³ Google Sheets offers function names in English and 21 other languages, switched with the “Always use English function names” option under File, Settings.¹⁵ A formula copied from an English-language tutorial, including this one, may need its names or separators changed before it works in your version.

Which lookup to use: a simple rule

The choice follows from two questions: does everyone who opens the file have XLOOKUP, and is the sheet new or inherited? The rule below applies Microsoft’s recommendation and the compatibility limits above; no study compares error rates across the three functions, so read it as a reasonable default rather than a measured result.

1. Use XLOOKUP where everyone has it

In Microsoft 365, Excel 2021 or later and Google Sheets, XLOOKUP is the easiest of the three to write correctly: exact match by default, a named return column, and a built-in message for missing keys. It can also search from the last row upward, which returns the most recent entry in a log kept in date order rather than the oldest.¹¹

2. Use INDEX-MATCH when the file must open in older Excel

INDEX-MATCH gives you left lookups and resistance to inserted columns in every version, at the cost of a longer formula. Always type the 0 in MATCH, or it falls back to an approximate match.

3. Keep VLOOKUP for legacy sheets, always with an exact match

VLOOKUP will keep working: Microsoft has said both VLOOKUP and HLOOKUP will continue to be supported.² A sheet that works needs no rewrite; when you edit one, check that every VLOOKUP ends in FALSE and that its column number still points where it should.

Fixing #N/A and other common lookup errors

Many lookup errors come from the data rather than the formula. The Microsoft and Google help pages for VLOOKUP list the same usual suspects, and the checks apply to INDEX-MATCH and XLOOKUP too: stray spaces, numbers stored as text, a range that shifts when copied, and a column number larger than the range.⁸

An #N/A on a key you can see in the list is often a mismatch you cannot see: “Apple “ with a trailing space is not “Apple”, and a code stored as text will not match the same code stored as a number. Microsoft suggests the TRIM and CLEAN functions to remove stray spaces and non-printing characters, and to keep number and date keys stored as numbers rather than text. Other errors point at the formula. VLOOKUP returns a reference error when the column number is larger than the number of columns in the range, and a wrong but plausible value usually means an approximate match on unsorted data. If results drift as you fill a formula down, lock the lookup range with dollar signs, as Microsoft recommends, so every copy searches the same cells.⁶

Lookup health check

• Exact match set: FALSE in VLOOKUP, 0 in MATCH
• Return column named, not counted, where possible
• Lookup range locked with dollar signs
• Spaces trimmed and keys stored as the same type
• Missing keys show a visible word, not a blank
• Everyone who opens the file has the functions used

Download PDF ↓

Get this checklist as a one-page PDF. We'll email you the download link.

Your emailAlso send me the weekly newsletter (launching soon)Email me the PDF

We use your address to send this link and, only if you tick the box, the newsletter. Privacy policy.

The bottom line

Use XLOOKUP when everyone who opens the file has Microsoft 365, Excel 2021 or later, or Google Sheets, and INDEX-MATCH when anyone might not. Leave working VLOOKUPs alone, but make sure each one asks for an exact match, because the approximate default and the counted column number are where lookups go quietly wrong.