Excel practice › Excel functions › VLOOKUP
VLOOKUP in Excel: how it works, with examples you can edit
VLOOKUP finds a value in the first column of a table and returns the value from another column of the same row. To get the price of product 1004 from a list in A2:D6, where price is the third column: =VLOOKUP(1004, A2:D6, 3, FALSE).
The last argument matters more than it looks: FALSE asks for an exact match. Leave it out and Excel returns the closest smaller value when the one you asked for is missing, without any warning.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value- What to look for: a value or, more often, the cell that holds it.
table_array- The table to search. VLOOKUP only searches its first column, so the range has to start at the column that holds the lookup values.
col_index_num- Which column of the table to return, counted from the table's first column (1). It is a position inside
table_array, not a sheet column letter. range_lookupFALSE= exact match.TRUE, or nothing at all, = approximate match, which needs the first column sorted ascending. Optional, and the default is the risky one.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | ID | Product | Price | Stock | Find ID | Price | |
| 2 | 1001 | Keyboard | 49 | 12 | 1004 | 65 | |
| 3 | 1002 | Mouse | 19 | 40 | |||
| 4 | 1004 | Headset | 65 | 0 | |||
| 5 | 1005 | Webcam | 38 | 7 | |||
| 6 | 1007 | Dock | 120 | 3 |
3 to 2 for the product name, or to 4 for the stock.How Excel reads it
- It takes the value in F2 (1004) and goes down the first column of A2:D6 until it finds it.
- In that row it counts columns from the left edge of the table: ID is 1, Product is 2, Price is 3, Stock is 4.
- It returns what's in column 3 of that row: 65.
The three ways it goes wrong
Each of these is live below. Fix the formula in the box and watch the cell.
1. The value you're looking for isn't in the table's first column. VLOOKUP never looks left, and never looks in the middle of the table. Searching A2:D6 for a product name fails, because column A holds IDs:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | ID | Product | Price | Stock | Find product | Price | |
| 2 | 1001 | Keyboard | 49 | 12 | Mouse | #N/A | |
| 3 | 1002 | Mouse | 19 | 40 | |||
| 4 | 1004 | Headset | 65 | 0 | |||
| 5 | 1005 | Webcam | 38 | 7 | |||
| 6 | 1007 | Dock | 120 | 3 |
Mouse is in column B, and the table starts at A. Start the table at the column that holds the names and recount: =VLOOKUP(F2,B2:D6,2,FALSE).2. The column number is bigger than the table. A table of three columns has no column 4, so Excel answers #REF!. This happens when the table range is narrower than you think, or after someone deletes a column.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | ID | Product | Price | Stock | Find ID | Stock | |
| 2 | 1001 | Keyboard | 49 | 12 | 1005 | #REF! | |
| 3 | 1002 | Mouse | 19 | 40 | |||
| 4 | 1004 | Headset | 65 | 0 | |||
| 5 | 1005 | Webcam | 38 | 7 | |||
| 6 | 1007 | Dock | 120 | 3 |
A2:D6 and the stock (7) comes back.3. The last argument is missing. Without FALSE, VLOOKUP does an approximate match: if the value isn't there, it settles for the largest one below it. ID 1003 doesn't exist in this list, yet the formula returns a price, the one for 1002:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | ID | Product | Price | Stock | Find ID | Price | |
| 2 | 1001 | Keyboard | 49 | 12 | 1003 | 19 | |
| 3 | 1002 | Mouse | 19 | 40 | |||
| 4 | 1004 | Headset | 65 | 0 | |||
| 5 | 1005 | Webcam | 38 | 7 | |||
| 6 | 1007 | Dock | 120 | 3 |
,FALSE before the closing parenthesis and you get the honest answer: #N/A, this ID isn't in the list.Approximate match isn't a bug. It's the right tool for brackets, like a commission rate by sales tier or a grade by score, where you want "the row at or below this number". For looking up IDs, names or codes, always write FALSE.
Practice: 3 exercises
Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in number won't pass.
Someone types a product ID in F2. In G2, return how many of that product are in stock.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | ID | Product | Price | Stock | Find ID | Stock | |
| 2 | 1001 | Keyboard | 49 | 12 | 1005 | ||
| 3 | 1002 | Mouse | 19 | 40 | |||
| 4 | 1004 | Headset | 65 | 0 | |||
| 5 | 1005 | Webcam | 38 | 7 | |||
| 6 | 1007 | Dock | 120 | 3 |
Show the answer
=VLOOKUP(F2,A2:D6,4,FALSE) gives 7.
Stock is the 4th column of A2:D6, and FALSE makes sure only an exact ID counts.
ID 1003 isn't in the list, so a plain VLOOKUP shows #N/A. In G2, return the product name for the ID in F2, or the text Not found when the ID doesn't exist.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | ID | Product | Price | Stock | Find ID | Product | |
| 2 | 1001 | Keyboard | 49 | 12 | 1003 | ||
| 3 | 1002 | Mouse | 19 | 40 | |||
| 4 | 1004 | Headset | 65 | 0 | |||
| 5 | 1005 | Webcam | 38 | 7 | |||
| 6 | 1007 | Dock | 120 | 3 |
Show the answer
=IFERROR(VLOOKUP(F2,A2:D6,2,FALSE),"Not found") gives Not found.
IFERROR returns the VLOOKUP's result when there is one, and your text when the lookup ends in an error. IFNA works the same way and only catches #N/A, which is safer: other errors stay visible.
An order line: the ID is in F2 and the quantity in G2. In H2, calculate the order value: the product's price times the quantity.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | ID | Product | Price | Stock | Order ID | Qty | Value | |
| 2 | 1001 | Keyboard | 49 | 12 | 1001 | 3 | ||
| 3 | 1002 | Mouse | 19 | 40 | ||||
| 4 | 1004 | Headset | 65 | 0 | ||||
| 5 | 1005 | Webcam | 38 | 7 | ||||
| 6 | 1007 | Dock | 120 | 3 |
Show the answer
=VLOOKUP(F2,A2:D6,3,FALSE)*G2 gives 147.
A VLOOKUP returns a value like any other: once it gives 49, you can multiply it, compare it or pass it to another function.
Questions people ask
Why does VLOOKUP return #N/A when the value is in the table?
Almost always because the two values only look the same: one is a number and the other is text that looks like a number, or one has a space at the end. The other usual cause is a table range that doesn't start at the column being searched. VLOOKUP not working goes through each cause with a formula to fix.
Can VLOOKUP return a value from a column to the left?
No. It searches the first column of the table and returns from that column or one to its right. For a lookup to the left, use INDEX with MATCH or XLOOKUP.
Should I use VLOOKUP or XLOOKUP?
XLOOKUP is easier to get right: exact match is its default, it can look in any direction, and it doesn't break when a column is inserted. It isn't available in Excel 2016 or Excel 2019, though, and VLOOKUP is. If you share files with people on older versions, or don't know which Excel a test will use, know both.
What does the 0 at the end of some VLOOKUP formulas mean?
The same as FALSE: exact match. =VLOOKUP(F2,A2:D6,3,0) and =VLOOKUP(F2,A2:D6,3,FALSE) are the same formula.