VLOOKUP has been the go-to lookup formula for decades, and it still works. XLOOKUP is the newer version, and for most everyday lookups it’s easier to write and harder to break.
The same lookup, both ways
Say you have a product ID in cell A2 and want the price from a Prices tab, where IDs are in column A and prices are in column C.
VLOOKUP: =VLOOKUP(A2, Prices!A:C, 3, FALSE)
XLOOKUP: =XLOOKUP(A2, Prices!A:A, Prices!C:C, "Not found")
Why XLOOKUP is easier to live with
- No column counting. VLOOKUP needs a column number (the 3 above). If someone inserts a column on the Prices tab, that number quietly points at the wrong column. XLOOKUP points straight at the column you want.
- Exact match by default. Forget the FALSE at the end of a VLOOKUP and it does an approximate match, which can return a wrong value without any warning. XLOOKUP looks for an exact match unless you tell it otherwise.
- It can look left. VLOOKUP only returns columns to the right of the one it searches. XLOOKUP doesn’t care which side the answer is on.
- A built-in “not found” message. Instead of a #N/A error, you can show something friendlier, like “Not found.”
When to stick with VLOOKUP
XLOOKUP works in Microsoft 365, Excel 2021 and newer, Excel on the web, and Google Sheets. It doesn’t exist in Excel 2019 or older. If the file goes to someone on an older version, they’ll see errors where your lookups should be, so VLOOKUP is the safer choice there.
The bottom line
For new files, use XLOOKUP. For old files that work fine, there’s no need to rewrite every VLOOKUP. Switch them when you’re already making changes, or when one breaks.