Excel practice › Excel errors › VLOOKUP not working
VLOOKUP not working: 8 causes, in the order worth checking
A VLOOKUP that "doesn't work" does one of three things: it shows #N/A for a value you can see in the table, it returns the wrong row, or it shows something that isn't a result at all (a 0, or the formula itself). When it's #N/A and the value is there, the two values only look the same: one is a number and the other is text, or one has a space at the end.
When it's the wrong row, look at the last argument first. Write FALSE so only an exact match counts: =VLOOKUP(F2,A2:D7,2,FALSE).
The 8 causes, in the order worth checking
- A number on one side, text on the other. Result: #N/A.
- A space before or after the value. Result: #N/A.
- The table doesn't start at the column being searched. Result: #N/A.
- The last argument, FALSE, is missing. Result: the wrong row, with no error.
- The column number points at the wrong column. Result: the wrong value, or #REF!.
- The formula was copied down and the table range moved with it. Result: #N/A a few rows down.
- The cell it found is empty. Result: 0.
- The cell is formatted as text, or Show Formulas is on. Result: the formula itself, as text.
Causes 2 to 6 are reproduced below in sheets you can edit: the formula is broken, and the caption says how to repair it. Causes 1, 7 and 8 depend on how Excel stores and displays a cell, so they come with the steps to check them in your own file.
1. Numbers stored as text (or the other way around)
To Excel, the number 2107 and the text "2107" are two different values, and VLOOKUP won't match one with the other. It happens when one list is typed by hand and the other comes out of another system: account numbers, ZIP codes and invoice numbers often arrive as text. Formatting doesn't settle it either: two cells can look identical and hold different kinds of value.
How to tell: text sits on the left of its cell and numbers on the right, and Excel often marks a number stored as text with a small green triangle in the corner. To test a whole column, count the numbers in it. COUNT ignores text, so six account numbers that count as 0 are six pieces of text:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Account | Customer | Numbers in A | |
| 2 | 2101 | Harbor Dental | 0 | |
| 3 | 2104 | Lakeside Auto | ||
| 4 | 2107 | Maple Bakery | ||
| 5 | 2112 | Northgate Gym | ||
| 6 | 2115 | Pine St Vet | ||
| 7 | 2120 | Summit Realty |
COUNT to COUNTA, which counts every filled cell, and you get 6.Fix the data when you can: select the column, click the warning icon next to it and choose Convert to Number. When the list isn't yours to change, convert the lookup value inside the formula so both sides are the same kind:
- The table holds numbers and your lookup value is text:
=VLOOKUP(VALUE(F2),A2:D7,2,FALSE) - The table holds text and your lookup value is a number:
=VLOOKUP(TEXT(F2,"0"),A2:D7,2,FALSE)
This is the one cause on the list that the sheets on this page can't reproduce: their lookups treat 2107 and "2107" as equal. Excel doesn't.
2. A space before or after the value
"Maple Bakery" and "Maple Bakery " are different texts. The extra space can't be seen in the cell, and it's common in values pasted from an email, a web page or an export. Here the name in F2 has a space after it:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Account | Customer | Rep | Credit limit | Find customer | Credit limit | |
| 2 | 2101 | Harbor Dental | Kim | 5000 | Maple Bakery | #N/A | |
| 3 | 2104 | Lakeside Auto | Ortiz | 12000 | |||
| 4 | 2107 | Maple Bakery | Kim | 2500 | |||
| 5 | 2112 | Northgate Gym | Shah | 8000 | |||
| 6 | 2115 | Pine St Vet | Ortiz | 4000 | |||
| 7 | 2120 | Summit Realty | Shah | 15000 |
=LEN(F2) says 13. Wrap the lookup value in TRIM, which removes the spaces at both ends: =VLOOKUP(TRIM(F2),B2:D7,3,FALSE) returns 2500.When the space is in the table and not in the value you're looking up, TRIM on F2 changes nothing. Below, the stray space is in B6:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Account | Customer | Rep | Credit limit | Find customer | Rep | |
| 2 | 2101 | Harbor Dental | Kim | 5000 | Pine St Vet | #N/A | |
| 3 | 2104 | Lakeside Auto | Ortiz | 12000 | |||
| 4 | 2107 | Maple Bakery | Kim | 2500 | |||
| 5 | 2112 | Northgate Gym | Shah | 8000 | |||
| 6 | 2115 | Pine St Vet | Ortiz | 4000 | |||
| 7 | 2120 | Summit Realty | Shah | 15000 |
=TRIM(B2), copied down, then pasted back over the names as values. To fix it inside the formula, trim the whole table: =VLOOKUP(F2,TRIM(B2:D7),2,FALSE) returns Ortiz. In Excel 2019 and older, confirm that one with Ctrl+Shift+Enter.3. The table doesn't start at the column being searched
VLOOKUP searches only the first column of the range you give it. If the range starts at the account numbers and you look for a customer name, the name is never found, even though it's one column over.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Account | Customer | Rep | Credit limit | Find customer | Rep | |
| 2 | 2101 | Harbor Dental | Kim | 5000 | Northgate Gym | #N/A | |
| 3 | 2104 | Lakeside Auto | Ortiz | 12000 | |||
| 4 | 2107 | Maple Bakery | Kim | 2500 | |||
| 5 | 2112 | Northgate Gym | Shah | 8000 | |||
| 6 | 2115 | Pine St Vet | Ortiz | 4000 | |||
| 7 | 2120 | Summit Realty | Shah | 15000 |
=VLOOKUP(F2,B2:D7,2,FALSE) returns Shah. VLOOKUP can't return something to the left of the search column. For the account number of a customer, use INDEX with MATCH: =INDEX(A2:A7,MATCH(F2,B2:B7,0)) returns 2112.4. FALSE is missing: the wrong row, and no error
Leave out the fourth argument and VLOOKUP does an approximate match: when the value isn't in the first column, it settles for the largest value below it. Nothing warns you. Account 2110 doesn't exist in this list:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Account | Customer | Rep | Credit limit | Find account | Customer | |
| 2 | 2101 | Harbor Dental | Kim | 5000 | 2110 | Maple Bakery | |
| 3 | 2104 | Lakeside Auto | Ortiz | 12000 | |||
| 4 | 2107 | Maple Bakery | Kim | 2500 | |||
| 5 | 2112 | Northgate Gym | Shah | 8000 | |||
| 6 | 2115 | Pine St Vet | Ortiz | 4000 | |||
| 7 | 2120 | Summit Realty | Shah | 15000 |
,FALSE before the closing parenthesis and the formula tells the truth: #N/A, there is no account 2110.Approximate match also expects the first column to be sorted in ascending order. On an unsorted list it can return the wrong row even for a value that is in the list. Microsoft's VLOOKUP page gives this as the reason for a wrong value: sort the first column, or use FALSE. A 0 as the last argument means the same as FALSE.
5. The column number points at the wrong column
The third argument counts columns from the left edge of the table range. It isn't a sheet column letter, and it doesn't follow the data: insert a column in the table, or start the range one column later, and the same number points somewhere else.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Account | Customer | Rep | Credit limit | Find account | Credit limit | |
| 2 | 2101 | Harbor Dental | Kim | 5000 | 2112 | Shah | |
| 3 | 2104 | Lakeside Auto | Ortiz | 12000 | |||
| 4 | 2107 | Maple Bakery | Kim | 2500 | |||
| 5 | 2112 | Northgate Gym | Shah | 8000 | |||
| 6 | 2115 | Pine St Vet | Ortiz | 4000 | |||
| 7 | 2120 | Summit Realty | Shah | 15000 |
6. The formula was copied down and the table moved
A formula that works in the first row and shows #N/A a few rows down has a table range without $ signs. Copying a formula down moves every reference down with it, the table included:
| Typed in G2 | The same formula after copying to G4 |
|---|---|
=VLOOKUP(F2,A2:D7,2,FALSE) | =VLOOKUP(F4,A4:D9,2,FALSE) |
F2 became F4, which is what you want. A2:D7 became A4:D9, which is not: the first two accounts are no longer in the table.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Account | Customer | Rep | Credit limit | Account | Customer | |
| 2 | 2101 | Harbor Dental | Kim | 5000 | 2112 | Northgate Gym | |
| 3 | 2104 | Lakeside Auto | Ortiz | 12000 | 2120 | Summit Realty | |
| 4 | 2107 | Maple Bakery | Kim | 2500 | 2101 | #N/A | |
| 5 | 2112 | Northgate Gym | Shah | 8000 | |||
| 6 | 2115 | Pine St Vet | Ortiz | 4000 | |||
| 7 | 2120 | Summit Realty | Shah | 15000 |
=VLOOKUP(F4,$A$2:$D$7,2,FALSE) returns Harbor Dental. In Excel, click on the range in the formula and press the F4 key to add the dollar signs.7. It returns 0: the cell it found is empty
A VLOOKUP that shows 0 has usually worked. It found the row, and the cell in the return column is empty: a formula that points at an empty cell shows 0. Look at that row of the table first. If the cell should hold something, fill it in. If it's right for it to be empty and you want the result to look empty too, test for it:
=IF(VLOOKUP(F2,A2:D7,4,FALSE)="","",VLOOKUP(F2,A2:D7,4,FALSE))
Then check the column number (cause 5): the 0 can also be a real 0 from the wrong column.
8. The cell shows the formula instead of a result
If the cell displays =VLOOKUP(F2,A2:D7,2,FALSE) as plain text, the lookup itself may be fine. Excel isn't calculating the cell. Two things to check:
- Show Formulas is on. Press Ctrl+` (the key above Tab) to switch it off, or use the Show Formulas button on the Formulas tab.
- The cell was formatted as Text before the formula was typed. Set the format to General (Ctrl+1 opens Format Cells), then press F2 and Enter to enter the formula again.
A formula that doesn't start with = is treated as text too.
Practice: 4 broken formulas to fix
Each task shows a formula that doesn't work on the sheet below it. Type the one that does and press Enter. Clicking a cell inserts its address. Answers are checked on the data you see and again on changed data.
F2 holds a customer name pasted from an email, with a space after it. =VLOOKUP(F2,B2:D7,3,FALSE) returns #N/A. In G2, write the formula that returns the credit limit anyway.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Account | Customer | Rep | Credit limit | Find customer | Credit limit | |
| 2 | 2101 | Harbor Dental | Kim | 5000 | Pine St Vet | ||
| 3 | 2104 | Lakeside Auto | Ortiz | 12000 | |||
| 4 | 2107 | Maple Bakery | Kim | 2500 | |||
| 5 | 2112 | Northgate Gym | Shah | 8000 | |||
| 6 | 2115 | Pine St Vet | Ortiz | 4000 | |||
| 7 | 2120 | Summit Realty | Shah | 15000 |
Show the answer
=VLOOKUP(TRIM(F2),B2:D7,3,FALSE) gives 4000.
TRIM removes the spaces before and after the text, so the lookup value is the same text as the name in column B.
=VLOOKUP(F2,A2:D7,3,FALSE) should return the rep for the customer in F2. It returns #N/A. In G2, write the formula that works.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Account | Customer | Rep | Credit limit | Find customer | Rep | |
| 2 | 2101 | Harbor Dental | Kim | 5000 | Lakeside Auto | ||
| 3 | 2104 | Lakeside Auto | Ortiz | 12000 | |||
| 4 | 2107 | Maple Bakery | Kim | 2500 | |||
| 5 | 2112 | Northgate Gym | Shah | 8000 | |||
| 6 | 2115 | Pine St Vet | Ortiz | 4000 | |||
| 7 | 2120 | Summit Realty | Shah | 15000 |
Show the answer
=VLOOKUP(F2,B2:D7,2,FALSE) gives Ortiz.
VLOOKUP only searches the first column of its range. Starting the range at B makes Customer the search column, and Rep becomes column 2.
There is no account 2110, and =VLOOKUP(F2,A2:D7,2) answers Maple Bakery, which is account 2107. In G2, write a formula that returns the customer for the account in F2, or the text No such account when the account isn't in the list.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Account | Customer | Rep | Credit limit | Find account | Customer | |
| 2 | 2101 | Harbor Dental | Kim | 5000 | 2110 | ||
| 3 | 2104 | Lakeside Auto | Ortiz | 12000 | |||
| 4 | 2107 | Maple Bakery | Kim | 2500 | |||
| 5 | 2112 | Northgate Gym | Shah | 8000 | |||
| 6 | 2115 | Pine St Vet | Ortiz | 4000 | |||
| 7 | 2120 | Summit Realty | Shah | 15000 |
Show the answer
=IFNA(VLOOKUP(F2,A2:D7,2,FALSE),"No such account") gives No such account.
FALSE makes VLOOKUP accept the exact account number only, so a missing account gives #N/A and not a neighbor's name. IFNA turns that #N/A into a message and leaves every other error visible.
G2 held =VLOOKUP(F2,A2:D7,4,FALSE). Copied down two rows, it became =VLOOKUP(F4,A4:D9,4,FALSE), and G4 shows #N/A. In G4, write the formula as it should have been copied, with the table locked by $ signs.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Account | Customer | Rep | Credit limit | Account | Credit limit | |
| 2 | 2101 | Harbor Dental | Kim | 5000 | 2112 | 8000 | |
| 3 | 2104 | Lakeside Auto | Ortiz | 12000 | 2120 | 15000 | |
| 4 | 2107 | Maple Bakery | Kim | 2500 | 2104 | ||
| 5 | 2112 | Northgate Gym | Shah | 8000 | |||
| 6 | 2115 | Pine St Vet | Ortiz | 4000 | |||
| 7 | 2120 | Summit Realty | Shah | 15000 |
Show the answer
=VLOOKUP(F4,$A$2:$D$7,4,FALSE) gives 12000.
The dollar signs pin the table to rows 2 to 7 wherever the formula is copied. F4 has none, so it still moves with each row, which is what the lookup value should do.
Questions people ask
Why is my VLOOKUP returning #N/A when the value exists?
Because the value in the table and the value you're looking up aren't identical, or the formula isn't searching where the value is. Check in this order: is one a number and the other text (cause 1), does either have a space at the end (cause 2), does the table range start at the column that holds the value (cause 3), and does the range still cover the whole list (cause 6).
Why is my VLOOKUP returning the wrong value?
Check the fourth argument first. If it's missing, VLOOKUP does an approximate match and returns a nearby row: write FALSE as the last argument. If FALSE is there, check the column number: it counts from the first column of the table range. If the lookup value appears more than once in the first column, VLOOKUP returns the first one it meets.
Why is my VLOOKUP returning 0?
It found the row, and the cell it returns from is empty. Excel shows 0 for a formula that points at an empty cell. Fill in the cell, or check that the column number points at the column you meant.
Why is VLOOKUP not working and only showing the formula?
The cell is formatted as Text, or Show Formulas is switched on. Press Ctrl+` to switch Show Formulas off. If that isn't it, set the cell's format to General, then press F2 and Enter.
Why is VLOOKUP not working between two workbooks?
The same eight causes apply. Start with cause 1: two files from different sources often store the same ID as a number in one and as text in the other. One more thing can go wrong between files: the reference itself. The workbook name goes in square brackets, a sheet name with spaces needs single quotes around it, and when the other workbook is closed the reference needs its full path. Select the table with the mouse while both files are open and Excel writes the reference for you.