How to look up a value from another table in Excel
A lookup finds a row by a value you know, such as a product code, and returns something from the same row, such as its price.
XLOOKUP is the modern way to do it. VLOOKUP and INDEX/MATCH do the same job in older spreadsheets.
The formula
| A | B | C | |
|---|---|---|---|
| 1 | Code | Product | Price |
| 2 | P-10 | Lamp | 45 |
| 3 | P-20 | Desk | 210 |
| 4 | P-30 | Chair | 120 |
=XLOOKUP("P-20",A2:A4,C2:C4)Result: 210
Finds P-20 in the code column, and returns the price from the same row.
Step by step
- Type =XLOOKUP( and give it the value to find, usually a cell.
- Select the column to search.
- Select the column to return.
- Close the bracket and press Enter.
Other ways to write it
=VLOOKUP("P-20",A2:C4,3,FALSE)The classic version. FALSE at the end means exact match, and you nearly always want it.=INDEX(C2:C4,MATCH("P-20",A2:A4,0))Works in every version of Excel, and can look to the left.
Each of these gives the same result on the example above, in Excel and in Cellwise.
Common mistakes
- Leaving off FALSE in VLOOKUP
- Without it, VLOOKUP assumes the list is sorted and can return the wrong row without any warning.
- Counting columns wrongly
- VLOOKUP’s column number counts from the first column of the table, not from column A.
Reading isn’t the same as doing
Find it with XLOOKUP gives you real tasks on this, and tests your formulas on data you haven’t seen. It’s free, with no sign-up.