VLOOKUP Not Working? 8 Causes and Fixes

When VLOOKUP is not working, the formula is almost always fine and the data is the problem. A hidden space, a number stored as text or a range that moved is enough to turn a correct lookup into #N/A or a wrong answer. Work through the eight causes below in order, and you will find the fix for most broken lookups in a few minutes.

The examples use this staff list in A1:C6, with the name to find in F2:

A useful tool for every cause below is Evaluate Formula. Click the formula cell and go to Formulas > Evaluate Formula, then click Evaluate to watch Excel work out each part in turn. You can see the exact lookup value it uses, hidden spaces included, and the step where the error first appears.

1. The fourth argument is missing, so the match is approximate#

If you leave out the last argument, VLOOKUP uses an approximate match. On unsorted data it can return a value from the wrong row without any error. Always add FALSE (or 0) for an exact match:

=VLOOKUP(F2,A2:C6,3,FALSE)

With "Priya Nair" in F2, this returns 2231. Approximate match is only for sorted bands, such as tax brackets or grade boundaries. The beginner's VLOOKUP guide explains both modes.

2. Extra spaces hide in the data#

A trailing space makes "Priya Nair " a different value from "Priya Nair", so VLOOKUP returns #N/A even though you can see the name. Spaces often arrive with data copied from websites or exported from other systems.

To test, use LEN: =LEN(F2) returns 10 for "Priya Nair" and 11 if there is a hidden space. To fix the lookup value, wrap it in TRIM:

=VLOOKUP(TRIM(F2),A2:C6,3,FALSE)

TRIM removes spaces at the start and end and turns double spaces into single ones. If the spaces are in the table instead, clean that column with TRIM in a helper column. See the TRIM function guide for details.

3. Numbers are stored as text#

To Excel, the number 2104 and the text "2104" are different values, so they never match. This happens a lot with IDs imported from other systems. Text numbers usually sit on the left of the cell and may show a small green triangle.

Fix the formula to match the table's type:

Joining &"" turns a number into text, and VALUE turns text into a number. You can also convert the whole column once with the warning triangle's Convert to Number option.

4. The lookup value is not in the first column#

VLOOKUP only searches the first column of the table you give it. If you want to find a department and return the employee name, =VLOOKUP("IT",A2:C6,1,FALSE) fails, because column A holds names, not departments.

Start the table at the column you search instead, such as B2:C6, and count the return column from there. VLOOKUP can never return a column to the left of the search column. For that, use XLOOKUP or INDEX MATCH. If INDEX MATCH gives you trouble too, read why INDEX MATCH stops working.

5. The table range moves when you copy the formula#

A formula that works in the first row and fails lower down usually has an unlocked range. When you copy =VLOOKUP(F2,A2:C6,3,FALSE) down one row, it becomes A3:C7, which no longer includes row 2.

Lock the table with dollar signs. Click the range in the formula bar and press F4 (Mac: ⌘T) to add them:

=VLOOKUP(F2,$A$2:$C$6,3,FALSE)

Leave F2 unlocked so each row looks up its own value.

6. The column index number is wrong#

The third argument counts columns from the left edge of the table, not from column A. A number larger than the table's width returns #REF!, a number below 1 returns #VALUE!, and a typed number goes wrong when someone inserts a column. The guide to the VLOOKUP column index number shows how to count it and how to make it update with MATCH.

7. The formula shows as text or doesn't update#

If the cell shows =VLOOKUP(...) instead of a result, the cell was formatted as Text before you typed. Change the format to General (Home > Number Format), click into the cell, and press Enter.

If the result doesn't change when the data changes, calculation may be set to Manual. Go to Formulas > Calculation Options > Automatic, or press F9 to recalculate once. Also check that Show Formulas is off (Windows: Ctrl+`; Mac: ⌃`).

8. The value really is missing#

Sometimes #N/A is the right answer: the ID or name just isn't in the table. Once you have ruled out causes 2 and 3, show a clear message instead of the error with IFNA:

=IFNA(VLOOKUP(F2,$A$2:$C$6,3,FALSE),"Not found")

Use IFNA rather than IFERROR here. IFNA only hides "not found" results, while IFERROR also hides real mistakes, such as a #REF! from a bad column number. Hiding errors before you understand them is a common trap in job tests, and it often comes up in VLOOKUP interview questions. The same data problems also break counting formulas, as the guide to COUNTIF not working shows. The VLOOKUP function guide has the full syntax.

Practise it#

The fastest way to get good at fixing lookups is to fix broken ones. Practise fixing lookup errors in a live spreadsheet: each task has a #N/A or wrong answer, and you find the cause and repair the formula. If you need a refresher on the basics first, practise VLOOKUP step by step, then test yourself with the intermediate Excel mock test.