Verdict: use XLOOKUP when everyone who opens the file has Excel 2021 or later, Microsoft 365 or Excel for the web. Use VLOOKUP when the workbook must open in Excel 2019 or older, or when you are keeping a sheet that already works. XLOOKUP is the easier one to get right, because it is exact by default and does not count columns.
Functions
XLOOKUP vs VLOOKUP: Which to Use, with Examples
Ready to practice?
Apply what you just learned with interactive exercises.
XLOOKUP and VLOOKUP both find a value in one column and return a value from the same row. The difference is in how much they ask of you. This page puts them side by side on one small price list, then shows when each is the better choice.
The short answer
- XLOOKUP takes the column to search and the column to return as two separate ranges, so it can return a value from the left or the right of the lookup column.
- VLOOKUP searches the first column of a table and returns a value a counted number of columns to the right. It cannot look left.
- XLOOKUP is exact by default and has a built-in message for a missing value. VLOOKUP is approximate unless you type FALSE.
Side by side
| VLOOKUP | XLOOKUP | |
| Syntax | =VLOOKUP(lookup, table, col_number, [range_lookup]) | =XLOOKUP(lookup, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) |
| Looks left of the lookup column | No | Yes |
| Match type if you leave the last argument out | Approximate | Exact |
| Return column | A number you count, which does not follow if you insert a column | A range you select |
| When nothing matches | #N/A, unless you wrap it in IFERROR | Your own text in if_not_found |
| Search from the bottom up | No | Yes, with search_mode set to -1 |
| Excel versions | All | Excel 2021 or later, Microsoft 365, Excel for the web |
The same lookup, both ways
Here is a small price list. The SKU to find, A103, is typed in E2.
| A | B | C | |
| 1 | SKU | Product | Price |
| 2 | A101 | Notebook | 4.50 |
| 3 | A102 | Pen | 1.20 |
| 4 | A103 | Stapler | 8.90 |
| 5 | A104 | Folder | 2.10 |
| 6 | A105 | Scissors | 6.40 |
=VLOOKUP(E2,A2:C6,3,FALSE)
=XLOOKUP(E2,A2:A6,C2:C6)
Both return 8.90. In the VLOOKUP formula the 3 means the third column of A2:C6 and FALSE asks for an exact match. In the XLOOKUP formula you point at the column to search and the column to return, and exact match is already on.
Now look left: find the SKU for a product name typed in F2.
=XLOOKUP(F2,B2:B6,A2:A6)
VLOOKUP cannot do this one, because SKU is to the left of Product. With VLOOKUP you would have to move the columns or switch to INDEX and MATCH.
To show your own message when the SKU is not on the list:
=XLOOKUP(E2,A2:A6,C2:C6,"Not found")
=IFERROR(VLOOKUP(E2,A2:C6,3,FALSE),"Not found")
Which one should you use?
Choose XLOOKUP for new work, for lookups to the left, for a clear message when a value is missing, and when you want the last match instead of the first (set search_mode to -1).
Choose VLOOKUP when the file has to open in Excel 2019 or older, when you are editing a sheet that already uses it, or when the key is the first column and you want the shorter, familiar formula.
Choose neither when you need a lookup that works in every version and also looks left. That is the job of INDEX and MATCH, compared with XLOOKUP in XLOOKUP vs INDEX MATCH.
Practice the difference
Reading about lookups does not make them stick. In the interactive XLOOKUP lesson you type the formula into a real sheet and it is marked as you go, and the VLOOKUP lesson does the same for the older function. If you want a path rather than one function, the Excel courses start free, and each one ends in a marked exam. More on each function: XLOOKUP and VLOOKUP.
FAQ
Is XLOOKUP better than VLOOKUP?
For most new work, yes. XLOOKUP looks in either direction, is exact by default and has a built-in message for a missing value. VLOOKUP is still the right choice when the workbook has to open in Excel 2019 or older, because XLOOKUP is not available there.
Does XLOOKUP work in Excel 2019?
No. XLOOKUP is in Excel 2021 and later, Microsoft 365 and Excel for the web. In Excel 2019 or older the formula shows a name error, so use VLOOKUP, or INDEX and MATCH, in files that older versions must open.
Can VLOOKUP look to the left?
No. VLOOKUP searches the first column of the table you give it and can only return a value from that column or one to its right. To look left, use XLOOKUP, or combine INDEX and MATCH.
Why does VLOOKUP return the wrong value when I leave out the last argument?
The last argument, range_lookup, is approximate by default. Approximate match expects the first column to be sorted and can return a nearby value instead of an error. Type FALSE (or 0) as the fourth argument to ask for an exact match.
Keep going
Guides people read next.
XLOOKUP Excel: How to Use It, With Examples and Fixes
Learn how to use XLOOKUP in Excel to find a value, return any column, show your own message and match ranges, with worked examples and fixes.
21 minINDEX MATCH vs VLOOKUP: Which Lookup Should You Use?
INDEX MATCH vs VLOOKUP compared: looking left, inserting columns, two-way lookups and how much each formula asks you to type. One worked example, one verdict.
5 minXLOOKUP vs INDEX MATCH: Which Is Better for Lookups?
XLOOKUP vs INDEX MATCH compared: one function or two, Excel versions, not-found messages and two-way lookups. One short worked example and a clear verdict.
5 min