Both functions find a value in a table and bring back something from the same row. For example, you type a product code and get the price. They work differently, and the newer one is easier to get right.
How VLOOKUP works
VLOOKUP searches the first column of a table and returns a value from a column to the right. You give it the column by number. A typical formula looks like this:
=VLOOKUP(A2, Products!A:C, 3, FALSE)
This says: find the code in A2 in the first column, and return the third column. The problem is that the number 3 breaks if someone inserts a column. It also cannot look to the left.
How XLOOKUP works
With XLOOKUP you pick the column to search and the column to return, separately:
=XLOOKUP(A2, Products[Code], Products[Price], "Not found")
It can look in any direction. It finds an exact match by default, and the last part lets you show your own message when nothing matches. Inserting a column does not break it.
Which versions have it?
XLOOKUP is in Excel 2021 and Microsoft 365. It is not in Excel 2019 or older. VLOOKUP works in every version.
A simple rule
If everyone who opens the file has Excel 2021 or Microsoft 365, use XLOOKUP. If you share files with people on older versions, use VLOOKUP or the INDEX and MATCH pair, which works everywhere.
We cover both in week 2 of our Excel intermediate course, along with drop-down lists and conditional formatting.
About the author
Aub Thompson runs AST Digital in Coffs Harbour. He taught adults in TAFE NSW for two decades and has worked in IT for three.
