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).

Skip to the 4 exercises ↓

The 8 causes, in the order worth checking

  1. A number on one side, text on the other. Result: #N/A.
  2. A space before or after the value. Result: #N/A.
  3. The table doesn't start at the column being searched. Result: #N/A.
  4. The last argument, FALSE, is missing. Result: the wrong row, with no error.
  5. The column number points at the wrong column. Result: the wrong value, or #REF!.
  6. The formula was copied down and the table range moved with it. Result: #N/A a few rows down.
  7. The cell it found is empty. Result: 0.
  8. 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:

Live exampleCOUNT finds no numbers in column A
D2fx
ABCD
1AccountCustomerNumbers in A
22101Harbor Dental0
32104Lakeside Auto
42107Maple Bakery
52112Northgate Gym
62115Pine St Vet
72120Summit Realty

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:

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:

Live example#N/A: a space after the name in F2
G2fx
ABCDEFG
1AccountCustomerRepCredit limitFind customerCredit limit
22101Harbor DentalKim5000Maple Bakery #N/A
32104Lakeside AutoOrtiz12000
42107Maple BakeryKim2500
52112Northgate GymShah8000
62115Pine St VetOrtiz4000
72120Summit RealtyShah15000

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:

Live example#N/A: the space is in the table (B6)
G2fx
ABCDEFG
1AccountCustomerRepCredit limitFind customerRep
22101Harbor DentalKim5000Pine St Vet#N/A
32104Lakeside AutoOrtiz12000
42107Maple BakeryKim2500
52112Northgate GymShah8000
62115Pine St Vet Ortiz4000
72120Summit RealtyShah15000

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.

Live example#N/A: names are in B, the table starts at A
G2fx
ABCDEFG
1AccountCustomerRepCredit limitFind customerRep
22101Harbor DentalKim5000Northgate Gym#N/A
32104Lakeside AutoOrtiz12000
42107Maple BakeryKim2500
52112Northgate GymShah8000
62115Pine St VetOrtiz4000
72120Summit RealtyShah15000

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:

Live exampleNo FALSE: a customer for an account that doesn't exist
G2fx
ABCDEFG
1AccountCustomerRepCredit limitFind accountCustomer
22101Harbor DentalKim50002110Maple Bakery
32104Lakeside AutoOrtiz12000
42107Maple BakeryKim2500
52112Northgate GymShah8000
62115Pine St VetOrtiz4000
72120Summit RealtyShah15000

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.

Live exampleA rep where the credit limit should be
G2fx
ABCDEFG
1AccountCustomerRepCredit limitFind accountCredit limit
22101Harbor DentalKim50002112Shah
32104Lakeside AutoOrtiz12000
42107Maple BakeryKim2500
52112Northgate GymShah8000
62115Pine St VetOrtiz4000
72120Summit RealtyShah15000

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 G2The 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.

Live example#N/A in the third row of a copied formula
G4fx
ABCDEFG
1AccountCustomerRepCredit limitAccountCustomer
22101Harbor DentalKim50002112Northgate Gym
32104Lakeside AutoOrtiz120002120Summit Realty
42107Maple BakeryKim25002101#N/A
52112Northgate GymShah8000
62115Pine St VetOrtiz4000
72120Summit RealtyShah15000

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:

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.

Exercise 1A space in the lookup value

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.

G2fx
ABCDEFG
1AccountCustomerRepCredit limitFind customerCredit limit
22101Harbor DentalKim5000Pine St Vet
32104Lakeside AutoOrtiz12000
42107Maple BakeryKim2500
52112Northgate GymShah8000
62115Pine St VetOrtiz4000
72120Summit RealtyShah15000
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.

Exercise 2The table starts in the wrong column

=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.

G2fx
ABCDEFG
1AccountCustomerRepCredit limitFind customerRep
22101Harbor DentalKim5000Lakeside Auto
32104Lakeside AutoOrtiz12000
42107Maple BakeryKim2500
52112Northgate GymShah8000
62115Pine St VetOrtiz4000
72120Summit RealtyShah15000
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.

Exercise 3FALSE is missing

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.

G2fx
ABCDEFG
1AccountCustomerRepCredit limitFind accountCustomer
22101Harbor DentalKim50002110
32104Lakeside AutoOrtiz12000
42107Maple BakeryKim2500
52112Northgate GymShah8000
62115Pine St VetOrtiz4000
72120Summit RealtyShah15000
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.

Exercise 4Copied down without locking the table

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.

G4fx
ABCDEFG
1AccountCustomerRepCredit limitAccountCredit limit
22101Harbor DentalKim500021128000
32104Lakeside AutoOrtiz12000212015000
42107Maple BakeryKim25002104
52112Northgate GymShah8000
62115Pine St VetOrtiz4000
72120Summit RealtyShah15000
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.

Keep going

Lesson 3 is six VLOOKUP drills on a coffee-shop menu: the column number, the first-column rule, IFERROR, locking the table with $ before copying a formula down, and VLOOKUP inside IF. It's part of Pro ($29 once for everything, or $19 for the Excel track). Lessons 1 and 2 and the level test are free.

Related

VLOOKUP function, with live examples#N/A error in Excel: meaning and fixes#REF! error in Excel: meaning and fixesXLOOKUP function, with live examplesINDEX MATCH, with live examplesTRIM function, with live examplesExcel formula errors, fixed liveExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: VLOOKUP function, Microsoft Support: How to correct a #N/A error, Microsoft Support: How to avoid broken formulas in Excel, Microsoft Support: Convert numbers stored as text to numbers in Excel. Last checked November 2026.