VLOOKUP looks for a value in the first column of a table, then returns a value from another column on the same row. Every version of Excel has it, so you’ll meet it in plenty of workbooks.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| Argument | What it does |
|---|---|
| lookup_value | What to look for, such as a product code. |
| table_array | The table. VLOOKUP searches its first column. |
| col_index_num | Which column of the table to return, counting the table’s first column as 1. |
| range_lookup | FALSE (or 0) for an exact match. Leave it out, or use TRUE, and VLOOKUP looks for an approximate match in sorted data, which can return the wrong row. |
=VLOOKUP(A2,F2:H6,3,FALSE) looks for ST-3302 in F2:F6, the first column of the table, and returns 9.99 from the table’s third column, H.
Type a formula, then check it. Hints step in when you’re stuck, and the last one shows the answer.
In C2, use VLOOKUP to find the price for the code in A2.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Code | Qty | Price | Code | Product | Price | ||
| 2 | ST-3302 | 4 | (answer cell) | NB-2041 | Notebook | 3.50 | ||
| 3 | NB-2041 | 10 | PN-1180 | Pens | 4.20 | |||
| 4 | FD-1005 | 30 | ST-3302 | Stapler | 9.99 | |||
| 5 | DL-4410 | 1 | DL-4410 | Desk lamp | 24.00 | |||
| 6 | FD-1005 | Folders | 1.80 |
Scroll the sheet sideways to see every column.
Nice. Try the next task
Look up every price in C2:C5 with VLOOKUP. Lock the table so it stays put when the formula fills down.
Write the formula for C2. We’ll fill it down to C5 for you.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Code | Qty | Price | Code | Product | Price | ||
| 2 | ST-3302 | 4 | (answer cell) | NB-2041 | Notebook | 3.50 | ||
| 3 | NB-2041 | 10 | (answer cell) | PN-1180 | Pens | 4.20 | ||
| 4 | FD-1005 | 30 | (answer cell) | ST-3302 | Stapler | 9.99 | ||
| 5 | DL-4410 | 1 | (answer cell) | DL-4410 | Desk lamp | 24.00 | ||
| 6 | FD-1005 | Folders | 1.80 |
Scroll the sheet sideways to see every column.
That’s both. Practise the whole course free.
Each message below is the checker’s real reply to that formula on the example data.
=VLOOKUP(A2,F2:H6,3)
The checker says: Add FALSE as the last argument. Without it, VLOOKUP looks for an approximate match and can return the wrong row.
Without FALSE, VLOOKUP assumes the first column is sorted and can return a price from the wrong row.
=VLOOKUP(A2,F2:H6,8,FALSE)
The checker says: The column number is larger than the table. The table has 3 columns.
Price is in column H of the sheet, but it’s column 3 of the table. Count from the table’s first column.
=VLOOKUP(A2,G2:H6,2,FALSE)
The checker says: VLOOKUP searches the first column of the table, here column G, Product. Start the table at column F, Code.
VLOOKUP only searches the first column of the table. If the column you need is to the left, use XLOOKUP.
=VLOOKUP(A2,F2:H6,3,FALSE)
The checker says: C2 works, but the copies can’t find their codes. When you fill down, the table moves too. Lock it with $: $F$2:$H$6.
Filled down, the table slides down too and misses the first rows. Lock it with $ before you fill.
It couldn’t find the value in the table’s first column. Check for typing differences, extra spaces, numbers stored as text, or a table that starts in the wrong column.
No. It only returns columns to the right of the one it searches. XLOOKUP, or INDEX with MATCH, can look either way.
Use XLOOKUP when everyone who opens the file has Excel 2021 or Microsoft 365. Use VLOOKUP when the workbook has to work in older versions.
VLOOKUP is part of module 8 of Excel Foundations: Look things up.
Excel Foundations is free, with no sign-up. Your progress is saved in this browser.