XLOOKUP vs VLOOKUP: which should you learn for an Excel test?
XLOOKUP is the newer, more flexible lookup function, but VLOOKUP is still everywhere. If you have a test coming up and limited time, here's what you need to know about both.
The same lookup, both ways
Suppose product codes are in column F and prices are in column H. Here's how each function finds the price for the code in A2:
- VLOOKUP: =VLOOKUP(A2, $F$2:$H$20, 3, FALSE)
- XLOOKUP: =XLOOKUP(A2, $F$2:$F$20, $H$2:$H$20)
XLOOKUP asks for two things: where to search, and where to get the answer. You don't have to count columns, and it looks for an exact match by default.
The main differences
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Search column | Must be the first column of the range | Can be anywhere |
| Default match | Approximate, unless you add FALSE | Exact |
| If a column is inserted | The column number can point to the wrong place | Keeps working, because it uses ranges |
| If nothing is found | Shows #N/A, so you add IFERROR | Has its own option for a message |
| Availability | Works in old and new versions | Only in newer versions |
Which to learn first
If your time is short, start with VLOOKUP. It's been around for decades, it works in every version, and you'll run into it in existing workbooks. If a test is built on an older version, XLOOKUP may not be available at all.
Then learn XLOOKUP, which should only take a few minutes once you understand lookups. If the test lets you choose, use whichever you can write correctly without stopping to think. You may also see INDEX with MATCH, an older combination that can search in any direction too.
More guides
DeskReady is an independent practice tool, not affiliated with any software vendor or employer. Tests vary, so use this as a general guide.