Lookups: VLOOKUP & XLOOKUP
Lookups find a value in one table and pull back matching information — like a search that returns an answer.
What you will learn
- Explain what a lookup does
- Use VLOOKUP to fetch a matching value
- Use the simpler, newer XLOOKUP
What a lookup is for
Imagine a price list, and separately a list of orders that only has product names. A lookup searches the price list for each product and brings back its price. It is like asking, “find this item in that table and tell me its price.”
The price-list example
Here is a small price list in columns A and B:
| A | B | |
|---|---|---|
| 1 | Product | Price |
| 2 | Apples | 50 |
| 3 | Bananas | 30 |
| 4 | Cherries | 90 |
VLOOKUP: the classic
VLOOKUP (vertical lookup) searches down the first column of a table and returns a value from a column to its right. It needs four parts:
- The value to find (for example the product name).
- The table to search in (the range).
- The column number to return from (1 is the first column of the table, 2 the second, and so on).
- Whether you want an exact match — for that, put
FALSE.
=VLOOKUP("Bananas", A2:B4, 2, FALSE)Note: Output: 30 VLOOKUP searched column A for Bananas, found it in row 3, then returned the value from the second column of the table, which is the price 30.
Watch out: Always end VLOOKUP with FALSE for an exact match. If you leave it off (or use TRUE), Excel does an approximate match and can return the wrong row. VLOOKUP also cannot look to the left of the search column.
XLOOKUP: the easier, newer way
XLOOKUP does the same job but is simpler and more flexible. You give it three clear parts: what to find, where to look (the lookup column), and what to return (the result column). No counting columns, and it is exact by default.
=XLOOKUP("Bananas", A2:A4, B2:B4)Note: Output: 30 XLOOKUP matched Bananas in the lookup range A2:A4 and returned the value in the same position from the result range B2:B4. No column numbers needed, and it matches exactly by default.
| VLOOKUP | XLOOKUP | |
|---|---|---|
| You give it | value, table, column number, FALSE | value, lookup range, result range |
| Exact match | You must add FALSE | Exact by default |
| Can look left? | No | Yes |
| Easier to read? | Trickier (count columns) | Yes |
When the item is not found: #N/A
If a lookup cannot find what you asked for, it does not stay blank — it shows the error #N/A, which means “not available, I could not find it.” This is normal and expected. To show a friendly message instead, wrap the lookup in IFERROR, which says “try this; if it errors, show that.”
=IFERROR(XLOOKUP("Mango", A2:A4, B2:B4), "Not found")Note: Output: Not found There is no Mango in the price list, so the lookup returned #N/A. IFERROR caught that error and showed Not found instead — much friendlier in a report than a cryptic #N/A. Works the same way around a VLOOKUP.
Tip: If your Excel has XLOOKUP (Excel 2021 and Microsoft 365, and Google Sheets), prefer it — it is clearer and avoids VLOOKUP’s common traps. Learn VLOOKUP too, because you will still meet it in older sheets.
Q. In =VLOOKUP("Cherries", A2:B4, 2, FALSE), what does the number 2 mean?
✍️ Practice
- Build the three-row price list above, then use VLOOKUP to fetch the price of Apples.
- Do the same lookup with XLOOKUP and confirm you get the same answer.
🏠 Homework
- Make a 6-item product/price table and a separate list of 4 order product names. Use a lookup to pull each order’s price next to it.