Excel VLOOKUP and XLOOKUP
Lookup functions find a value in one table and bring back related information, like finding a product's price from its code.
The Example Table
A price list on a sheet, in A1:C5:
| A | B | C | |
|---|---|---|---|
| 1 | Code | Product | Price |
| 2 | P101 | Basmati Rice 5kg | 1850 |
| 3 | P102 | Cooking Oil 1L | 560 |
| 4 | P103 | Tea 900g | 1650 |
| 5 | P104 | Sugar 1kg | 160 |
On the bill, the cashier types only a code in F2, and Excel finds the product and price.
VLOOKUP
Example
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])| Argument | Meaning |
|---|---|
| lookup_value | What to look for, e.g. the code in F2 |
| table_array | The table, with the lookup column first |
| col_index_num | Which column of the table to return (1, 2, 3...) |
| range_lookup | FALSE for an exact match, TRUE for an approximate match |
Find the product name and price for the code in F2:
Example
=VLOOKUP(F2, $A$2:$C$5, 2, FALSE)
=VLOOKUP(F2, $A$2:$C$5, 3, FALSE)| F2 | Product | Price |
|---|---|---|
| P103 | Tea 900g | 1650 |
Always use FALSE (exact match) for codes, names and IDs. If you leave it out, Excel uses approximate match and can return a wrong row without any error.
Limits of VLOOKUP
- It only searches the first column of the table and returns values to its right
- The column number breaks if someone inserts a new column in the table
- It returns
#N/Awhen the value is not found
XLOOKUP
XLOOKUP (Excel 2021, Microsoft 365 and Excel for the web) fixes these problems:
Example
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])The same lookups with XLOOKUP:
Example
=XLOOKUP(F2, A2:A5, B2:B5)
=XLOOKUP(F2, A2:A5, C2:C5, "Code not found")| F2 | Product | Price |
|---|---|---|
| P102 | Cooking Oil 1L | 560 |
| P999 | #N/A | Code not found |
Why XLOOKUP is better:
- Exact match is the default
- The return column can be on the left or the right
- It does not break when columns are inserted
- It has a built-in "not found" message
It can even look up from right to left, for example finding the code from a product name:
Example
=XLOOKUP("Sugar 1kg", B2:B5, A2:A5)| Result |
|---|
| P104 |
Approximate Match: Grade Tables
Approximate match is useful for ranges, like discount levels. The table must be sorted from smallest to largest:
| H | I | |
|---|---|---|
| 1 | From (Rs) | Discount |
| 2 | 0 | 0% |
| 3 | 5000 | 5% |
| 4 | 10000 | 10% |
Example
=VLOOKUP(B2, $H$2:$I$4, 2, TRUE)| Bill | Discount |
|---|---|
| 3200 | 0% |
| 7500 | 5% |
| 15000 | 10% |
Excel finds the largest "From" value that is not bigger than the bill.
INDEX and MATCH
Before XLOOKUP, experienced users combined INDEX and MATCH. It works in every version of Excel:
Example
=INDEX(C2:C5, MATCH(F2, A2:A5, 0))MATCH(F2, A2:A5, 0)finds the position of the code (0 means exact match)INDEX(C2:C5, position)returns the price in that position
| F2 | Result |
|---|---|
| P101 | 1850 |
Handling Not Found
With VLOOKUP, wrap it in IFERROR:
Example
=IFERROR(VLOOKUP(F2, $A$2:$C$5, 3, FALSE), "Not found")If you get #N/A even though the value looks correct, check for extra spaces (use TRIM) or numbers stored as text.
Exercise
Exercise
In =VLOOKUP(F2, A2:D20, 3, FALSE), what does the 3 mean?