XLOOKUP vs VLOOKUP: which to use, and when it matters

Short answer: use XLOOKUP if you have it. It is easier to write, harder to break, and does three things VLOOKUP cannot do at all.

The one reason not to is not a feature. XLOOKUP arrived in Excel 365 and Excel 2021. Excel 2019, 2016 and earlier do not have it, and they never will. Open a file full of XLOOKUP in Excel 2019 and every one of them shows #NAME?.

So the real question is not which function is better. It is who opens this file.

The same lookup, both ways

Find the price of P300.

That difference looks cosmetic and it is not. The number 3 in the VLOOKUP is a promise about the shape of the table. Insert a column and the promise is broken. Nothing warns you. XLOOKUP holds an address, and Excel moves addresses when the sheet moves.

Three things only XLOOKUP does

It looks left

VLOOKUP searches the first column of its range and returns something to the right. Always. To find the code from the product name, VLOOKUP needs the columns rearranged.

=XLOOKUP("Chain",B2:B4,A2:A4) returns P300. The two ranges are independent, so direction stops being a rule.

It has a not-found answer built in

=XLOOKUP("P900",A2:A4,C2:C4,"No such code").

With VLOOKUP you wrap the whole formula in IFNA to do the same job. That is a second function around the first. The fourth argument is simply easier to read six months later.

It is exact by default

VLOOKUP's fourth argument is optional and defaults to TRUE, which means approximate. Leave it out on an unsorted table and you get the wrong row with no error.

This one design decision is behind a large share of wrong spreadsheets. XLOOKUP defaults to exact. You have to ask for approximate on purpose.

Side by side

The last row, which VLOOKUP cannot reach

A price list with a history has several rows per product, oldest first, and you want the newest.

VLOOKUP returns the first match it finds. There is no argument to change that. =XLOOKUP("P300",A2:A99,C2:C99,,0,-1) searches from the bottom and returns the last one. The empty argument is the not-found message, the 0 is exact match, and the -1 is the direction.

What to do if you are stuck on an older version

Use INDEX and MATCH. It works in every version of Excel, it looks left, and it holds no column number.

=INDEX(C2:C4,MATCH("P300",A2:A4,0))

MATCH finds the row number. INDEX returns that row from the column you name. It is two functions instead of one. Experienced users chose it over VLOOKUP long before XLOOKUP existed.

So which one should you write?

• Only you open the file, and you have Excel 365: XLOOKUP.
• You send the file to people whose Excel you do not control: INDEX and MATCH. It works everywhere and breaks nothing.
• You are learning, and want the one every interview asks about: VLOOKUP first, then the other two. It is still the function people ask about.

Questions people ask

Is XLOOKUP slower than VLOOKUP?

On a normal sheet you will not notice. On hundreds of thousands of rows, any exact-match lookup is slow. It checks each row in turn. The fast trick there is an approximate match on sorted data, which both functions support.

Will my XLOOKUP break when a colleague opens it in Excel 2016?

Yes. They see #NAME? in every cell. The values are still there until the file is recalculated and saved; after that they are gone. This is the whole reason INDEX and MATCH is still the safe answer for a shared file.

Does XLOOKUP replace INDEX and MATCH?

For most jobs, yes. INDEX and MATCH still wins for a two-way lookup. There you find a row and a column at once. It is also the version that opens anywhere.

Why does my XLOOKUP return a 0 instead of nothing?

It found the row, and the cell it returned is empty. An empty cell read by a formula is a zero. Wrap it if that matters: =IF(XLOOKUP(...)=0,"",XLOOKUP(...)), or format the cell to hide zeros.