The short answer
Both functions find a value in one place and bring back a matching value from somewhere else. XLOOKUP is the newer, more flexible function and is the better choice if everyone using your workbook has Microsoft 365 or Excel 2021 or later. VLOOKUP still matters because it works in every version of Excel and appears in a huge number of existing workbooks.
Side-by-side comparison
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Default match type | Approximate (unless you set FALSE) |
Exact |
| Can look to the left | No, only to the right | Yes, in any direction |
| Breaks if you insert a column | Yes, the column number goes out of date | No, it uses ranges rather than a column number |
| Custom “not found” message | Needs IFERROR around it |
Built in |
| Search from the bottom up | No | Yes |
| Available in | Every version of Excel, Google Sheets | Microsoft 365, Excel 2021 and later, Google Sheets |
The example data
Imagine a small product list in cells A1 to C6:
| Product ID | Product | Price (£) |
|---|---|---|
| P-101 | Pen | 1.20 |
| P-102 | Pencil | 0.80 |
| P-103 | Ruler | 2.50 |
| P-104 | Notebook | 3.75 |
| P-105 | Stapler | 6.40 |
We want the price of product P-104.
How VLOOKUP works
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
For our example:
=VLOOKUP("P-104", A2:C6, 3, FALSE)
This looks for P-104 in the first column of A2:C6, then returns the value from the third column of that range, which is the price: 3.75.
How XLOOKUP works
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
For the same example:
=XLOOKUP("P-104", A2:A6, C2:C6, "Not found")
You point to the column to search (A2:A6) and the column to return (C2:C6) separately. If the ID doesn’t exist, you get the friendly message “Not found” instead of #N/A.
Looking to the left
Now suppose you know the product name and want its ID. The ID column is to the left of the name, which VLOOKUP can’t handle on its own. XLOOKUP can:
=XLOOKUP("Notebook", B2:B6, A2:A6)
This returns P-104.
Which one should you use?
- Use XLOOKUP for new work when you and everyone you share with have Microsoft 365, Excel 2021 or later, or Google Sheets.
- Use VLOOKUP when the workbook must open in older versions of Excel, or when you’re maintaining an existing workbook that already relies on it.
- Know both. You’ll meet VLOOKUP in workplace spreadsheets for years to come, so being able to read and fix it is a useful skill.
Try it yourself
- Type the product table above into a new sheet.
- Use VLOOKUP to find the price of P-102.
- Use XLOOKUP to find the price of P-105, with “Not found” as the message.
- Use XLOOKUP to find the ID of the Ruler.
- Change one formula to look up P-999 and compare what each function shows.