DEV Community

Daylongs
Daylongs

Posted on Originally published at daylongs.com

XLOOKUP vs VLOOKUP: switch for new files, keep the old ones

XLOOKUP is the newer lookup function, and for a workbook that only people on Microsoft 365 or Excel 2021 and later will open, I would pick it without much hesitation. VLOOKUP still runs in every version, but it finds its answer by counting columns, which makes it easy to break without any warning. If you only need the short answer, that is it. The rest of this post explains where each function trips people up.

The examples use a five-row price list with product IDs in column A. Both formulas return 2.20 for P-103, so the result matches. The real difference shows up a month later, when someone reads the formula, or when the data shifts underneath it.

Why does XLOOKUP read more clearly?

VLOOKUP ties the search to the first column of the range you select, so the key has to sit on the far left. The answer is chosen by a column number. In =VLOOKUP(F2,$A$2:$D$6,3,FALSE) you have to count A, B and C to know what comes back.

XLOOKUP names two separate ranges, one to search and one to return. =XLOOKUP(F2,$A$2:$A$6,$C$2:$C$6) says find it in column A and give me column C, and nobody has to count. The defaults are safer too. VLOOKUP quietly switches to an approximate match when you leave off the last argument, which can return a wrong row on an unsorted list. XLOOKUP matches exactly unless you ask for something else.

A laptop on a desk showing a spreadsheet with rows and columns of data

Formulas compared. See

How do you handle a missing key without hiding real errors?

Plenty of people wrap VLOOKUP in IFERROR, and that works, but IFERROR catches everything. A deleted column or a bad range gets reported as "not found" on every row, and the mistake can sit there for a week before anyone notices.

XLOOKUP's fourth argument reacts only to a missing match. =XLOOKUP(F4,$A$2:$A$6,$C$2:$C$6,"Not found") shows the text for an ID that is not in the table, while a broken range still shows its real error. The sixth argument handles repeated keys. Set it to -1 and Excel searches from the bottom up, which returns the latest row in a transaction log. The full argument reference table, with every match_mode and search_mode value and what each one does, sits in the original post, so you do not have to memorize the numbers.

Automate the repetitive part next:

Coffee cup beside a keyboard

#N/A fix steps. See

Which Excel versions can open an XLOOKUP file?

This is where people get burned. As of November 2026, Microsoft's documentation lists XLOOKUP for Microsoft 365, Excel 2021 and Excel 2024, and says it is not available in Excel 2016 or Excel 2019. I checked that through search summaries rather than the full support page, and the page's applies-to list has its own inconsistencies, so test it yourself. Type =XLOOKUP into the version you actually run. If the name does not autocomplete, you do not have the function.

The surprise is what an older install does with a newer file. A workbook saved in a current release opens fine in Excel 2019, but the XLOOKUP cells show #NAME? because that program does not recognize the function name. If the file goes to a vendor on an older install, ask about their version first, or paste the results as values before you send it.

Hands typing on a keyboard

Function list. See

When should you still reach for INDEX and MATCH?

INDEX with MATCH runs on much older Excel and handles left lookups without any trouble. =INDEX($C$2:$C$6,MATCH(F2,$A$2:$A$6,0)) returns 2.20 for P-103, the same answer as the other two formulas. MATCH finds the position of the ID, and INDEX pulls the price from that position.

My rule is simple. For a workbook you control, use XLOOKUP. For a file that goes to clients, vendors, or people whose version you cannot confirm, use INDEX and MATCH or send values. Leave working VLOOKUP formulas alone when they are not broken. Replace the ones that keep failing, the ones that need a left lookup, and everything you write from now on.

Dirty data breaks all three functions the same way, so check it first. A number stored as text will not match a real number, stray spaces make two identical-looking keys differ, and the lookups ignore case. A quick TRIM on the key column solves more problems than switching functions ever will.

Before you send any formula-heavy workbook outside your team, ask which Excel version they use. That one question prevents a long run of #NAME? emails.

Versions and behavior are as of November 2026.

Every argument value, plus the sample-table results for exact, wildcard and last-match lookups, is in the original:

See the argument reference table on daylongs.com

See the argument reference table on daylongs.com

Automate the repetitive part next: Also read: How to Automate

Top comments (0)