For any new lookup in a current version of Excel, use XLOOKUP. It does everything VLOOKUP does and fixes VLOOKUP’s sharp edges: it can look in any direction, it matches exactly by default, and it handles a missing value on its own.
VLOOKUP is not broken, and it keeps one real advantage: it works in every version of Excel, while XLOOKUP needs Microsoft 365 or Excel 2021 and later. So the honest answer is “XLOOKUP for new work, VLOOKUP when compatibility demands it.” Here’s the detail behind that.
The differences at a glance
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Where it can look | leftmost column only, returns to the right | any column, either direction |
| How it returns a value | a column number you count by hand | a return array you select |
| Default match | approximate (assumes sorted data) | exact |
| No match found | returns #N/A (wrap in IFNA) |
built-in if_not_found argument |
| Return more than one column | no | yes |
| Find the last match | no | yes (search from the bottom) |
| Works in Excel 2016 / 2019 | yes | no (Microsoft 365 / 2021+) |
The same lookup, written both ways
Say you want the department for the ID in D2, from this table:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | ID | Name | Dept | Find ID | Dept |
| 2 | E-01 | Amir | Finance | E-03 | Ops |
| 3 | E-02 | Bea | Sales | ||
| 4 | E-03 | Cai | Ops | ||
| 5 | E-04 | Dana | Finance |
With VLOOKUP you pass the whole table and count that Dept is the 3rd column, and you must remember FALSE for an exact match:
With XLOOKUP you name the search column and the return column directly, and exact is the default:
Both return Ops. The XLOOKUP version has nothing to miscount and no FALSE to forget.
Where XLOOKUP wins
- It looks in any direction. VLOOKUP can only return a column to the right of the one it searches. XLOOKUP takes a separate search array and return array, so the answer can sit on either side. To return the ID (column A) from a Dept lookup (column C),
=XLOOKUP(D2, C2:C5, A2:A5)just works. - Exact match by default. VLOOKUP defaults to an approximate match that silently returns the wrong row on unsorted data unless you add
FALSE. XLOOKUP matches exactly unless you tell it otherwise, so the common mistake can’t happen. - No column counting. A VLOOKUP like
=VLOOKUP(D2, A2:F5, 4, FALSE)breaks the moment someone inserts a column, because column 4 is now the wrong one. XLOOKUP points at the return column directly, so it moves when the column moves. - Built-in not-found handling. Instead of wrapping the whole formula in IFNA, XLOOKUP takes a fourth argument:
=XLOOKUP(D2, A2:A5, C2:C5, "Not found"). - It returns more than one column at once, and it can search from the bottom up to find the last match rather than the first.
If you’re mostly fighting the #N/A that VLOOKUP throws when it can’t match, the causes and fixes are in why your VLOOKUP returns #N/A, and the full feature tour is in the XLOOKUP guide.
Where VLOOKUP still makes sense
- Compatibility. VLOOKUP works in every version of Excel. XLOOKUP is only in Microsoft 365, Excel 2021, Excel 2024, and later, and it shows a
#NAME?error if the file is opened in Excel 2016 or 2019. If you share workbooks with people on older Excel, stick with VLOOKUP (or INDEX/MATCH, which is also everywhere). - It already works. There’s no prize for rewriting a correct VLOOKUP. Reach for XLOOKUP on new formulas and when you’re already reworking a sheet, not as a find-and-replace exercise.
How to convert a VLOOKUP to XLOOKUP
The swap is mechanical:
- Keep the lookup value (the first argument is the same).
- Point the second argument at just the lookup column,
A2:A5, not the wholeA2:C5table. - Add the return column as the third argument instead of counting to a column number.
- Delete the
FALSE, since XLOOKUP is exact by default.
So this:
becomes this:
Bottom line
New formula, current Excel: XLOOKUP. It looks any direction, matches exactly by default, survives inserted columns, and handles a missing value without an IFNA wrapper. Keep VLOOKUP when the file has to run in Excel 2016 or 2019, or when a working formula simply doesn’t need touching. Both of these search down a column; when your labels run across the top of a block instead, HLOOKUP is the horizontal version of the same idea. And if you migrate a shared model from one to the other, compare the two versions afterward so a silent change in a result can’t slip through.
