=VLOOKUP(F2,A2:D6,3,FALSE) looks for the value in F2 in the first column of A2:D6 and returns the value from the third column of the same row. FALSE at the end means "exact match only". Pick another product in F2 and the price changes.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
Click G2 to see the table A2:D6 outlined. Change the 3 in the formula to 2 and G2 returns the category instead of the price, because Category is the second column of the table. The match ignores case: pear finds Pear.
VLOOKUP syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| Argument | What it is | In the example |
|---|---|---|
lookup_value | The value to find. | F2 (Pear) |
table_array | The table to search. VLOOKUP looks only in its first column. | A2:D6 |
col_index_num | Which column of the table to return, counted from the table's first column (1). | 3 (Price) |
range_lookup | FALSE or 0 for an exact match. TRUE, 1 or nothing for an approximate match. | FALSE |
The column number counts from the start of the table, not from column A of the sheet. In a table that starts in column C, col_index_num 2 means column D. A number larger than the table is wide returns #REF!, and 0 returns #VALUE!.
In Excel set to a language that uses a decimal comma, arguments are separated by semicolons: =VLOOKUP(F2;A2:D6;3;FALSE).
Pick the return column with MATCH
A hard-coded 3 breaks quietly when someone inserts a column inside the table: the formula keeps returning the third column, which now holds something else. Let MATCH find the column number from the header instead. Here G1 is a drop-down: pick Stock or Category and G2 follows.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Carrot | 0.8 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
MATCH(G1,A1:D1,0) returns the position of "Price" in the header row, 3, and VLOOKUP uses it as the column number: 0.8 for Carrot. This is a two-way lookup: a row chosen by product, a column chosen by header. The same idea written with INDEX instead of VLOOKUP is on the INDEX and MATCH page.
Approximate match: VLOOKUP with TRUE
With TRUE as the last argument, VLOOKUP does not look for an equal value. It finds the largest value less than or equal to the lookup value. That is what you want for bands: tax brackets, grades, shipping rates, commission tiers. The first column must be sorted from smallest to largest.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sales from | Rate | Rep | Sales | Rate | |
| 2 | 0 | 0% | Ana | 750 | 0% | |
| 3 | 1000 | 3% | Ben | 4,200 | 3% | |
| 4 | 5000 | 5% | Cara | 5,000 | 5% | |
| 5 | 10000 | 8% | Dev | 12,500 | 8% |
Ben's 4,200 is not in column A. The largest value not above it is 1,000, so he gets 3%. Cara's 5,000 matches the 5,000 row exactly and gets 5%. Dev's 12,500 is above the last band and gets the last rate, 8%. A value below the first band (a negative sales figure here) returns #N/A, which is why the table starts at 0.
The $ signs in $A$2:$B$5 keep the table in place when F2 is filled down to F5. Without them, F3 would search A3:B6 and skip the first band.
Leaving out the fourth argument is the same as TRUE. On an unsorted product list that is a silent bug: Excel searches as if the list were sorted and can return a price from the wrong row, or #N/A for a value that is there. When you look up names, codes or IDs, always end with FALSE.
Why VLOOKUP returns #N/A
#N/A means "not found". The sheet below shows three common causes, and column G repeats each lookup wrapped in IFNA and TRIM.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | Fixed |
| 2 | Apple | Fruit | $1.20 | 40 | Kiwi | #N/A | Not found |
| 3 | Pear | Fruit | $1.50 | 25 | Milk | #N/A | $1.10 |
| 4 | Carrot | Vegetable | $0.80 | 60 | Fruit | #N/A | Not found |
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
#N/A The value you looked for is not in the lookup range.- The value is not in the table. Kiwi is not in A2:A6. That is a real "not found", and
IFNA(...,"Not found")turns it into readable text. Change E2 to Apple and both columns show the price. - Extra spaces. E3 holds
"Milk "with a trailing space, so it is not equal toMilk.TRIM(E3)removes it and G3 finds the price. If the spaces are in the table instead, clean column A with TRIM once rather than in every lookup. - The value is in another column. Fruit exists, but in column B. VLOOKUP only searches the first column of the table, so E4 fails in both columns. Start the table at the column you search, or use XLOOKUP, which takes the search column and the return column separately.
Use IFNA rather than IFERROR around a lookup. IFNA only catches #N/A, so a #REF! from a wrong column number still shows up instead of being hidden as "Not found".
Two more causes:
- Numbers stored as text. If column A holds product codes typed as text (often after an import, with a small green triangle in the corner) and F2 holds the number 101,
=VLOOKUP(F2,A2:B6,2,FALSE)returns #N/A even though 101 appears in the list. Convert one side:=VLOOKUP(F2&"",A2:B6,2,FALSE)searches for the text "101", and=VLOOKUP(VALUE(F2),A2:B6,2,FALSE)searches for a number when F2 is the text. - Approximate match on unsorted data, described in the section above.
VLOOKUP returns 0 instead of a blank
When the cell VLOOKUP lands on is empty, Excel shows 0, not an empty cell. A 0 in a Stock column then reads as "out of stock" when the stock was never entered. Add &"" to the formula, or test the length of the result:
=VLOOKUP(F2,A2:D6,4,FALSE)&""
=IF(LEN(VLOOKUP(F2,A2:D6,4,FALSE))=0,"",VLOOKUP(F2,A2:D6,4,FALSE))
The first is shorter but turns every number it returns into text, so a later SUM skips it. The second keeps numbers as numbers.
VLOOKUP from another sheet
Write the sheet name and ! before the table. When you build the formula in Excel, click the other sheet's tab and select the range: Excel writes Prices!A2:B6 for you. Here the Orders tab looks up prices on the Prices tab.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Product | Qty | Price | Total |
| 2 | 1001 | Pear | 3 | $1.50 | $4.50 |
| 3 | 1002 | Milk | 2 | $1.10 | $2.20 |
| 4 | 1003 | Apple | 5 | $1.20 | $6.00 |
| 5 | 1004 | Bread | 1 | $2.40 | $2.40 |
Open the Prices tab and change the price of Apple: the order total updates. Two details:
- A sheet name with spaces needs single quotes:
=VLOOKUP(B2,'Price list'!$A$2:$B$6,2,FALSE). - A table in another workbook adds the file name in brackets,
[Prices.xlsx]Prices!$A$2:$B$6. When that file is closed, Excel shows its full path in the formula and the lookup keeps working from the saved file.
VLOOKUP with wildcards (partial match)
With FALSE, the lookup value may contain wildcards: * stands for any number of characters and ? for exactly one. "*"&E2&"*" finds the first product whose name contains the text in E2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Contains | Price | |
| 2 | Green tea | Drinks | $3.20 | coffee | $2.90 | |
| 3 | Iced coffee | Drinks | $2.90 | |||
| 4 | Coffee beans | Pantry | $8.50 | |||
| 5 | Black tea | Drinks | $2.70 | |||
| 6 | Orange juice | Drinks | $3.40 |
"coffee" matches both Iced coffee and Coffee beans; VLOOKUP returns the first one from the top, 8.50, or to juice. To search for a real asterisk or question mark, put a tilde before it: "~*".
VLOOKUP to the left
VLOOKUP cannot return a column to the left of the column it searches: col_index_num counts to the right only, and negative numbers are an error. To find the product for a given price, search column C and return column A with XLOOKUP or INDEX and MATCH:
=XLOOKUP(2.4, C2:C6, A2:A6) Excel 2021 and Microsoft 365
=INDEX(A2:A6, MATCH(2.4, C2:C6, 0)) every version
Both return Bread on the first sheet's data. XLOOKUP has the full explanation.
Practice: shipping cost by weight
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Weight from (kg) | Cost | Weight (kg) | Cost | |
| 2 | 0 | $4.50 | 7 | ||
| 3 | 2 | $6.00 | |||
| 4 | 5 | $9.50 | |||
| 5 | 10 | $14.00 | |||
| 6 | 20 | $22.00 |
Your turn: Each cost applies from its weight up to the next weight in the list. In E2, use VLOOKUP to return the shipping cost of the parcel weight in D2.
VLOOKUP with two criteria
VLOOKUP takes one lookup value. To match on two columns, build a helper column that joins them, put it first in the table, and look up the same joined text. Column A below is =B2&"-"&C2 filled down, so it holds Coffee-Small, Coffee-Large and so on.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Key | Product | Size | Price | Product | Size | Price |
| 2 | Coffee-Small | Coffee | Small | $2.50 | Tea | Large | |
| 3 | Coffee-Large | Coffee | Large | $3.50 | |||
| 4 | Tea-Small | Tea | Small | $2.00 | |||
| 5 | Tea-Large | Tea | Large | $3.00 | |||
| 6 | Juice-Small | Juice | Small | $3.00 |
Your turn: Column A joins product and size with a dash. In G2, return the price for the product in E2 and the size in F2.
The separator matters: "Tea"&"Large" gives TeaLarge, which matches nothing in column A. In Excel 2021 and Microsoft 365 you can skip the helper column with =XLOOKUP(1,(B2:B6=E2)*(C2:C6=F2),D2:D6); lookup with multiple criteria shows that and the INDEX/MATCH version.
Frequently Asked Questions
How do I do a VLOOKUP in Excel?
Type =VLOOKUP( and give four arguments: the value to find, the table (its first column must hold that value), the number of the column to return, and FALSE for an exact match. =VLOOKUP("Pear",A2:D6,3,FALSE) finds Pear in column A and returns the value from column C of that row.
What does TRUE or FALSE mean at the end of VLOOKUP?
FALSE (or 0) asks for an exact match and returns #N/A when the value is missing. TRUE (or 1, or leaving the argument out) asks for an approximate match: the largest value less than or equal to the lookup value, which only works when the first column is sorted in ascending order.
Why does my VLOOKUP return #N/A?
The value was not found in the first column of the table. The usual causes are a typo, an extra space ("Milk " is not "Milk"), a number stored as text on one side only, or a value that sits in another column. Wrap the formula in IFNA to show your own text: =IFNA(VLOOKUP(F2,A2:D6,3,FALSE),"Not found").
Can VLOOKUP look to the left?
No. VLOOKUP only returns columns to the right of the first column of the table. Use =XLOOKUP(F2,C2:C6,A2:A6) in Excel 2021 or Microsoft 365, or =INDEX(A2:A6,MATCH(F2,C2:C6,0)) in any version.
How do I VLOOKUP from another sheet?
Put the sheet name and an exclamation mark before the range: =VLOOKUP(B2,Prices!$A$2:$B$6,2,FALSE). If the sheet name has a space, wrap it in single quotes: 'Price list'!$A$2:$B$6.
