VLOOKUP: a simple worked example
VLOOKUP looks for a value in the first column of a table, then brings back the value from another column on the same row. It's how you pull a price, a name or a team from one list into another.
A worked example
Here's a small price list. Each product has a code, a name and a price.
| A | B | C | |
|---|---|---|---|
| 1 | Code | Product | Price |
| 2 | P-204 | Stapler | £6.50 |
| 3 | P-117 | Desk lamp | £18.00 |
| 4 | P-310 | Box files | £4.20 |
| 5 | P-152 | Whiteboard | £35.00 |
| 6 | P-268 | Hole punch | £7.75 |
Someone orders product P-310. To find its price by hand, you would:
- Go down the first column until you find P-310. It's on the third row of the list.
- Stay on that row and move across to the third column, Price.
- Read the price: £4.20.
That's exactly what VLOOKUP does. You tell it what to look for, where the table is, which column to bring back, and that you want an exact match.
The formula
With the order codes in column A and the price list in E1:G6, the price for the code in A2 is:
=VLOOKUP(A2,$E$2:$G$6,3,FALSE)
A2is what to look for: the code on this order.$E$2:$G$6is the table. The codes must be in its first column.3is which column of the table to bring back. Count from the table's first column (E), not from column A: Code is 1, Product is 2, Price is 3.FALSEmeans exact matches only. Always include it unless you're working with bands (see below).
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | Qty | Price | Code | Product | Price | |
| 2 | P-310 | 4 | £4.20 | P-204 | Stapler | £6.50 | |
| 3 | P-152 | 1 | £35.00 | P-117 | Desk lamp | £18.00 | |
| 4 | P-204 | 2 | £6.50 | P-310 | Box files | £4.20 | |
| 5 | P-152 | Whiteboard | £35.00 | ||||
| 6 | P-268 | Hole punch | £7.75 |
Copying it down: lock the table with $
The dollar signs in $E$2:$G$6 keep the table in place when you copy the formula down. Without them, the copy in C3 would look in E3:G7, so the first product drops out of its table and some lookups fail with #N/A. Click on the table reference in the formula bar and press F4 to add the dollar signs (more on this in absolute references). The code, A2, has no dollar signs because it should move down a row each time.
Why VLOOKUP goes wrong
#N/A: it can't find the value
The value isn't in the table's first column, or it looks the same but isn't. The usual causes are a space at the end ("P-310 ") or a number stored as text. TRIM removes stray spaces. If a missing value is expected, wrap the lookup in IFERROR to show a message instead:
=IFERROR(VLOOKUP(A2,$E$2:$G$6,3,FALSE),"Not found")
A wrong answer with no error
You left off FALSE. Without it, VLOOKUP does an approximate match, which expects the first column to be sorted and can quietly bring back a value from the wrong row. Approximate match is only for bands, such as finding the grade for a score or the rate for a salary.
#REF!: the column number is too big
A table three columns wide has no column 4. Check the number against the table you selected, not the whole sheet.
It brings back the wrong column after you insert one
The column number is just a number, so inserting a new column inside the table doesn't update it. Check the 3 still points at the column you want.
It can't look to the left
VLOOKUP only searches the first column of the table and only brings back columns to its right. To find a code from a product name, use XLOOKUP or INDEX with MATCH.
Try it now
In C2, look up the price of each item from the price list in E1:G6. Your formula will be copied down to C7.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | Qty | Price | Code | Product | Price | |
| 2 | P-310 | 4 | P-204 | Stapler | £6.50 | ||
| 3 | P-152 | 1 | P-117 | Desk lamp | £18.00 | ||
| 4 | P-204 | 2 | P-310 | Box files | £4.20 | ||
| 5 | P-268 | 3 | P-152 | Whiteboard | £35.00 | ||
| 6 | P-117 | 1 | P-268 | Hole punch | £7.75 | ||
| 7 | P-310 | 6 |
Where it works
The same formula works in every version of Excel, in Google Sheets and in LibreOffice Calc. In Excel for Microsoft 365 and Excel 2021 onwards, XLOOKUP does the same job without counting columns, and can look left.