TL;DR:
VLOOKUP finds a value in the first column of a range and returns something from a column to its right. XLOOKUP separates the search column from the result column.
Use exact match (FALSE in VLOOKUP) unless you really want an approximate match.
XLOOKUP is not in older versions of Excel. Check your version before you share a file.
You have a list of orders with product codes, and a separate table with the price for each code. You need the price next to every order. Copying prices by hand is slow and breaks as soon as the price list changes. A lookup formula does it for you: give it a code, and it finds the matching row in the other table.
The idea behind a lookup
A lookup has three parts: the thing you are looking for, the place to look, and what to bring back. VLOOKUP and XLOOKUP both do this. They differ in how you point at the place to look and the thing to bring back.
VLOOKUP
The shape is VLOOKUP(what to find, table, column number, match type). It searches the first column of the table for the value, then returns the value in the numbered column of that row. The table must start with the column that contains your lookup values, and the result column must be to the right of it, because VLOOKUP counts columns from the left.
The match type is the part that trips people up. Use FALSE for an exact match. Leaving it out, or using TRUE, asks for an approximate match, which assumes your first column is sorted and can return a wrong row without warning.
XLOOKUP
The shape is XLOOKUP(what to find, where to look, what to return, [if not found]). You give two separate ranges: one to search and one to return from. They can be in any order, so a result to the left of the search column is fine. The default is an exact match. The optional last part lets you show your own text when nothing is found.
Which versions have XLOOKUP
XLOOKUP is not available in older versions of Excel. It is available in current Microsoft 365 and in newer standalone versions of Excel, and in Google Sheets. Because versions change, check your own: type =XLOOKUP( in a cell and see whether the app recognizes it. If you share a file with someone on an older version, they will see an error instead of a result, and VLOOKUP is the safer choice.
Worked example: finding a price by product code
Here is a small price list in cells A2 to C5.
Row | A (code) | B (product) | C (price) |
|---|---|---|---|
2 | A101 | Notebook | 3.50 |
3 | B204 | Marker | 1.25 |
4 | C310 | Folder | 0.80 |
5 | D415 | Stapler | 6.00 |
An order says the code is B204. To find its price:
=VLOOKUP("B204", A2:C5, 3, FALSE)
=XLOOKUP("B204", A2:A5, C2:C5, "Not found")Both return 1.25. Check by hand: B204 is in row 3 of the table, which is the Marker, and its price column is the third column, C. So the answer is 1.25. In practice you put the code in a cell, say E2, and write E2 instead of "B204". Then you can fill the formula down.
Use the result: an order of 12 markers costs 12 × 1.25 = 15.00. Substituting back, 15.00 ÷ 12 = 1.25, so the price you looked up is consistent.
When the code is not found
Now look up a code that is not in the list, such as Z999. VLOOKUP returns the error #N/A. XLOOKUP returns the text you chose, here "Not found". An #N/A is not a bug: it tells you the code is missing or has a typo, such as a trailing space. Do not hide it blindly. Show a clear message so someone can fix the data.
A lookup usually fails for one of three reasons. The code has a trailing space, one table holds a number as text while the other holds a true number, or the code is simply missing from the list. Repair the data itself rather than wrapping the formula in more patches.
Choosing between them
If everyone who opens the file has a version with XLOOKUP, prefer it. You point at real ranges instead of counting columns, the match is exact by default, and you can show your own message when nothing is found. Those three things remove the most common lookup mistakes.
If someone might open the file in an older version, use VLOOKUP with FALSE, and keep the lookup column on the left of the table. You can also keep the price list on its own sheet, so the formula always points at a clearly named place.
Either way, test your formula with three codes: one at the top of the list, one at the bottom, and one that does not exist. If all three behave as you expect, the formula will probably behave on the rest of the data too.
Mistakes to avoid
Forgetting FALSE in VLOOKUP and getting a wrong price.
Using the wrong column number after inserting a column. XLOOKUP is safer here because it points to a real range.
Letting the table range drift when you fill down. Anchor it with dollar signs, such as
$A$2:$C$5.Looking up in the wrong direction. VLOOKUP cannot return a column left of the search column.
For more spreadsheet habits that help lookups work, see cleaning up a messy spreadsheet and getting started with pivot tables.
Frequently asked questions
Should I learn VLOOKUP at all?
Yes, if you share files with people on older software. Many existing spreadsheets use it.
Why does my lookup return the wrong row?
Most often because the match type was left as approximate. Add FALSE in VLOOKUP.
Get started
