Excel VLOOKUP
VLOOKUP looks for a value in the first column of a table and returns the value from another column in the same row. For an exact match, write =VLOOKUP(what, table, column_number, FALSE).
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value- The value to find, usually a cell such as
F2. table_array- The table to search. VLOOKUP only searches its first column.
col_index_num- Which column of the table to return, counting from 1 at the left edge of the table (not of the sheet).
range_lookupFALSEfor an exact match.TRUE, or leaving it out, gives an approximate match, which needs the first column sorted in ascending order.
How VLOOKUP works
Think of how you look something up in a printed price list: you run your finger down the left column until you find the product, then slide across to the price. VLOOKUP does the same. The V stands for vertical, because it searches down a column.
In the sheet below, the products are in A2:A9 and the prices are in column C, the third column of the table A2:D9. To get the price of Cheese:
=VLOOKUP("Cheese", A2:D9, 3, FALSE)
- VLOOKUP searches down
A2:A9for"Cheese"and finds it in row 7. - It moves to column 3 of the table, which is column C.
- It returns
4.50.
In real sheets you rarely type the value into the formula. You point at a cell instead, such as =VLOOKUP(F2, A2:D9, 3, FALSE), so changing F2 changes the answer.
Exact or approximate match
Use FALSE almost every time. It means "find exactly this", and returns #N/A when the value is missing. TRUE is for bands such as discount tiers or tax brackets: VLOOKUP then finds the largest value that is less than or equal to the one you asked for. That only works when the first column is sorted from smallest to largest. Leaving the fourth argument out means TRUE, which is the most common source of wrong VLOOKUP results.
Practise VLOOKUP
Click a numbered cell, type a formula and press Enter. Each answer is checked straight away, then tested again on changed data, so the result has to come from the formula. While typing a formula, click or drag across cells to insert their references. Yellow cells are inputs you can change.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Product | Result | From qty | Discount | |||
| 2 | Apple | Fruit | 1.20 | 140 | Cheese | 0 | 0% | ||||
| 3 | Banana | Fruit | 0.50 | 260 | Bread | 10 | 5% | ||||
| 4 | Carrot | Vegetable | 0.80 | 95 | Mango | 50 | 10% | ||||
| 5 | Bread | Bakery | 2.40 | 30 | 100 | 15% | |||||
| 6 | Milk | Dairy | 1.10 | 75 | |||||||
| 7 | Cheese | Dairy | 4.50 | 22 | Order | Price | |||||
| 8 | Tomato | Vegetable | 1.60 | 110 | Tomato | ||||||
| 9 | Croissant | Bakery | 1.90 | 18 | Apple | ||||||
| 10 | Banana | ||||||||||
| 11 | Croissant | ||||||||||
| 12 | |||||||||||
| 13 | Quantity | Discount | |||||||||
| 14 | 64 | ||||||||||
| 15 | |||||||||||
| 16 |
-
1In
G2, find the price of the product named inF2.Show answer
=VLOOKUP(F2,A2:D9,3,FALSE) -
2In
G3, find how many of the product inF3are in stock.Show answer
=VLOOKUP(F3,A2:D9,4,FALSE) -
3
F4holds a product that is not in the list, so a plain VLOOKUP shows#N/A. InG4, look up its price but showNot foundwhen it is missing.Show answer
=IFERROR(VLOOKUP(F4,A2:D9,3,FALSE),"Not found") -
4Write one formula in
G8that finds the price of the product inF8. It is filled down toG11for you, and all four rows must be right.Show answer
=VLOOKUP(F8,$A$2:$D$9,3,FALSE) -
5The table in
I1:J5lists discount bands: 10 or more items get 5%, 50 or more get 10%, and so on. InG14, find the discount for the quantity inF14.Show answer
=VLOOKUP(F14,I2:J5,2,TRUE)
Common VLOOKUP problems
#N/A
The value was not found. Check for typing differences and extra spaces (TRIM removes them), and make sure numbers are not stored as text in one place and as numbers in the other. If a missing value is expected, wrap the formula in IFERROR or IFNA.
Wrong value with no error
Almost always the fourth argument is missing or TRUE, so VLOOKUP did an approximate match on unsorted data. Add FALSE.
#REF!
The column number is larger than the number of columns in the table. =VLOOKUP(F2, A2:B9, 3, FALSE) asks for column 3 of a two-column table.
Works in the first row, breaks when filled down
The table reference moved. Make it absolute with dollar signs: $A$2:$D$9.
Breaks when a column is inserted
The column number is a fixed count, so inserting a column inside the table makes it point at the wrong column. XLOOKUP and INDEX MATCH do not have this problem.
Questions
Can VLOOKUP look to the left?
No. VLOOKUP only searches the first column of the table and returns values to its right. To return a column to the left of the lookup column, use XLOOKUP or INDEX with MATCH.
What is the difference between VLOOKUP and XLOOKUP?
XLOOKUP takes a lookup column and a return column separately, so it can look left, does an exact match by default, and does not break when columns are inserted. It is available in Excel 2021, Excel for Microsoft 365 and Excel for the web. VLOOKUP works in every version.
Is VLOOKUP case-sensitive?
No. "cheese" and "Cheese" match each other.
Can VLOOKUP use wildcards?
Yes, with an exact match. =VLOOKUP("Cro*", A2:D9, 3, FALSE) finds the first product starting with Cro. A question mark matches a single character.
Why does VLOOKUP return the first match only?
VLOOKUP stops at the first row that matches. To return every match, use FILTER in Excel 365.