VLOOKUP vs XLOOKUP: which one should you use?
Both functions find a value in one column and return a matching value from the same row. XLOOKUP is the newer one and fixes most of the limits of VLOOKUP, but VLOOKUP is still everywhere in existing workbooks.
This guide shows the differences that matter and a simple way to choose.
VLOOKUP: look right, count columns
VLOOKUP always searches the first column of the table you give it, and returns a value from the column number you count. The last argument should be FALSE for an exact match; leaving it out gives an approximate match that needs sorted data.
Example: Price by product ID with VLOOKUP
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product ID | Product | Price | Stock | Product ID | P-102 | |
| 2 | P-101 | Desk lamp | 24.5 | 40 | Price | 149 | |
| 3 | P-102 | Office chair | 149 | 12 | |||
| 4 | P-103 | Monitor stand | 39.9 | 25 | |||
| 5 | P-104 | Keyboard | 59 | 0 |
The price is the third column of A2:D5. If someone inserts a column inside the table, the 3 can point at the wrong data.
XLOOKUP: any direction, no column numbers
XLOOKUP takes the column to search and the column to return as two separate ranges. They can be anywhere, so it can look to the left, and inserting columns doesn't break it. Exact match is the default.
Example: Look to the left with XLOOKUP
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product ID | Product | Price | Stock | ID of the Keyboard | |
| 2 | P-101 | Desk lamp | 24.5 | 40 | P-104 | |
| 3 | P-102 | Office chair | 149 | 12 | ||
| 4 | P-103 | Monitor stand | 39.9 | 25 | ||
| 5 | P-104 | Keyboard | 59 | 0 |
The ID is in column A, left of the name in column B. VLOOKUP can't return a column to the left of its search column.
Other differences
Missing values: VLOOKUP returns #N/A, so you wrap it in IFNA or IFERROR. XLOOKUP has a built-in 4th argument for the message.
Direction: XLOOKUP can search from the last item (search mode -1), useful for the latest entry. VLOOKUP finds the first match only.
Approximate matches: VLOOKUP needs data sorted ascending. XLOOKUP's match modes -1 and 1 work on unsorted data.
Versions: XLOOKUP exists in Excel 2021, Excel 2024, Microsoft 365 and Excel for the web. It does not exist in Excel 2019 or older, and the file shows #NAME? there. VLOOKUP works in every version.
A simple rule
Use XLOOKUP in new work when everyone who opens the file has Excel 2021 or newer. Use VLOOKUP (or INDEX with MATCH) when the file must open in older versions, and when you maintain existing VLOOKUP formulas, which still work.
Common mistakes
Leaving out FALSE in VLOOKUP. The lookup becomes approximate and can return a wrong row without any error.
Fix: Always write FALSE (or 0) as the last argument unless you want an approximate match.
Hard-coded column numbers. Inserting or deleting a column inside the table changes which column the number points at.
Fix: Use XLOOKUP, or MATCH for the column number, so the formula follows the headers.
Frequently asked questions
Is VLOOKUP going away?
No. Microsoft keeps it for compatibility, and it is still the most common lookup in existing workbooks. It is still worth knowing for tests and shared files.
Is XLOOKUP faster than VLOOKUP?
For everyday spreadsheets the difference is not noticeable. Choose by flexibility and by which Excel versions need to open the file.
Related functions
- VLOOKUPVLOOKUP finds a value in the first column of a table and returns a value from the same row in another column.
- XLOOKUPXLOOKUP searches one column or row and returns the matching value from another, in any direction, with a built-in 'not found' message.
- INDEXINDEX returns the value at a given row and column of a range.
- MATCHMATCH returns the position of a value in a list, for example that "Lena" is the 3rd name.
- IFERRORIFERROR returns a value you choose when a formula produces an error such as #N/A or #DIV/0!, and the normal result otherwise.