Excel VLOOKUP not working: how to fix #N/A, #REF! and wrong results

Applies to: VLOOKUP in Excel for Microsoft 365 (Windows and Mac), Excel 2024, 2021, 2019 and 2016, and Excel for the web. XLOOKUP, covered at the end, needs Microsoft 365, Excel 2024 or Excel 2021.

How VLOOKUP reads your formula

Many VLOOKUP failures come from one of its four arguments doing something different from what you expect, so it helps to know exactly what each one means. Microsoft’s VLOOKUP function page defines them like this:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: the value you’re searching for. It has to appear in the first (leftmost) column of table_array.
• table_array: the block of cells to search, for example $A$2:$D$500.
• col_index_num: which column of that block to return, counting the leftmost column as 1. It’s a position inside the range, not a worksheet column letter.
• range_lookup: FALSE (or 0) for an exact match, TRUE (or 1) for an approximate match. If you leave it out, Excel uses TRUE.

Throughout this guide, the examples use a made-up price list in A2:D500 (product code, name, category, price) and a code to look up in G2. A working exact-match formula for the price looks like this:

=VLOOKUP(G2,$A$2:$D$500,4,FALSE)

Match your symptom to a fix

What you see	Likely cause	Go to
#N/A on values you can see in the list	Text vs number mismatch, extra spaces, or missing FALSE	Sections 1, 3 and 4
#N/A on every row	The lookup column isn’t the first column of the range	Section 2
#REF!	col_index_num is larger than the number of columns in the range	Section 5
#VALUE!	col_index_num is less than 1, or an argument is the wrong type	Section 6
A value, but the wrong one	Approximate match on unsorted data, or a shifted column	Sections 7 and 9
Right in the first row, wrong further down	The range moved when the formula was copied	Section 8
Old result that doesn’t update	Calculation set to Manual	Section 10
#SPILL!	A whole column used as the lookup value	Section 11

1. #N/A: exact match vs approximate match

#N/A means VLOOKUP couldn’t find a match. With an exact match, that literally means the value isn’t in the first column of the range. With an approximate match, Microsoft says #N/A appears when the lookup value is smaller than the smallest value in that first column.

The catch is the default. A formula like =VLOOKUP(G2,$A$2:$D$500,4) has no fourth argument, so Excel treats it as an approximate match. For product codes, invoice numbers, email addresses or names, that’s almost never what you want. Add FALSE:

=VLOOKUP(G2,$A$2:$D$500,4,FALSE)

Microsoft’s #N/A troubleshooting page for VLOOKUP makes the same point: set range_lookup to FALSE for an exact match, because the approximate version can produce wrong answers as well as #N/A.

If the value genuinely isn’t in the list, #N/A is the correct answer. You can show something friendlier instead by wrapping the formula. IFNA only catches #N/A, so other errors such as #REF! still show and stay easy to spot:

=IFNA(VLOOKUP(G2,$A$2:$D$500,4,FALSE),"Not found")

Microsoft’s page suggests IFERROR for this, which also works but hides every error type. Only wrap the formula once you know the remaining #N/A results are real misses, not one of the problems below.

One more exact-match detail: VLOOKUP treats ? and * in a text lookup value as wildcards. If the codes you search for contain a literal asterisk or question mark, put a tilde (~) in front of it, as the VLOOKUP page explains.

2. The lookup value isn’t in the first column

VLOOKUP only searches the leftmost column of table_array and only returns values to the right of it. If your sheet has the product name in column B and the code in column C, a lookup by code with $A$2:$D$500 searches the names, finds nothing, and returns #N/A on every row.

You have three ways out:

• Start the range at the lookup column. Searching by code in column C and returning the price in D? Use $C$2:$D$500 with col_index_num 2.
• Move the column. Drag the lookup column so it becomes the first column of the block. Our guide on moving a row in Excel without overwriting data shows the Shift-drag method; with a whole column selected, the same drag moves the column.
• Use a function that can look left. If the value you want sits to the left of the lookup column, VLOOKUP can’t return it at all. XLOOKUP or INDEX/MATCH can; see the alternatives section.

3. Numbers stored as text

To Excel, the number 1045 and the text “1045” are different values, so an exact match between them fails even though the cells look identical. Microsoft’s #N/A page lists mismatched data types as a cause, and the VLOOKUP page warns against storing numbers or dates as text in the first column.

Signs to look for: numbers aligned to the left of the cell, a small green triangle in the corner, or values imported from another system, a CSV file or a web page. Select a cell and press Ctrl + 1 to see its format.

Fix the source data where you can:

• Select the affected cells, click the warning icon that appears, and choose Convert to Number.
• Or select the column and use Data > Text to Columns, then select Finish right away. Microsoft mentions Text to Columns as a way to apply a format to a whole column.

If you can’t change the source, convert the lookup value inside the formula instead. When G2 holds text but the first column holds numbers:

=VLOOKUP(VALUE(G2),$A$2:$D$500,4,FALSE)

When G2 holds a number but the first column holds text:

=VLOOKUP(TEXT(G2,"0"),$A$2:$D$500,4,FALSE)

The "0" format suits whole numbers. Codes with leading zeros, such as 00450, need a format with that many digits, for example "00000".

4. Extra spaces and hidden characters

A trailing space turns “SKU-200” into “SKU-200 ”, and the exact match fails. Spaces are hard to see, so a quick test helps: =LEN(G2) returns a higher count than the characters you can see if something extra is there.

Microsoft recommends TRIM for leading and trailing spaces and CLEAN for nonprinting characters. To clean the lookup value only:

=VLOOKUP(TRIM(G2),$A$2:$D$500,4,FALSE)

If the spaces are in the lookup column, clean that instead. Add a helper column with =TRIM(CLEAN(A2)), fill it down, then copy it and paste it back over column A as values. Microsoft’s page also shows TRIM applied to the whole range inside VLOOKUP, but notes that older Excel versions need that version entered as an array formula, so the helper column is simpler.

Text copied from web pages may contain non-breaking spaces, which TRIM doesn’t remove. If TRIM doesn’t change the LEN result, try replacing character 160 with a normal space first:

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))

5. #REF!: col_index_num is beyond the table

If col_index_num is bigger than the number of columns in table_array, VLOOKUP returns #REF!. A range of $A$2:$D$500 has four columns, so asking for column 5 fails:

=VLOOKUP(G2,$A$2:$D$500,5,FALSE)

Either lower the number, or widen the range to include the column you want, for example $A$2:$E$500. Remember that the count starts at the first column of the range, not at column A of the sheet. A range of $C$2:$F$500 also has only four columns, even though it ends in column F.

6. #VALUE! errors

According to Microsoft’s VLOOKUP page, a column number less than 1 returns #VALUE!. That happens with a typo such as 0, or when col_index_num comes from another formula or cell that ends up blank or zero. Check that it evaluates to a whole number of 1 or more; Formulas > Evaluate Formula steps through the calculation so you can see the value Excel actually uses.

7. Wrong results from unsorted data

An approximate match doesn’t hunt for the closest value. It assumes the first column is sorted in ascending order and returns the last row that isn’t larger than the lookup value. On unsorted data that assumption breaks, and VLOOKUP returns a value from a row that has nothing to do with your search, with no error to warn you.

Approximate match is the right tool for bands and tiers. Here a made-up table in J2:K5 holds the lower edge of each band (0, 50, 100, 250) and its discount:

=VLOOKUP(G2,$J$2:$K$5,2,TRUE)

For that to work, the first column must be sorted smallest to largest. Microsoft’s guidance is simple: either sort the first column, or switch to FALSE for an exact match. If you’re not deliberately looking up bands, use FALSE.

A related surprise: with an exact match, VLOOKUP returns the first matching row it meets. If the code appears twice, you’ll always get the upper one. Remove duplicates or add a second condition with a different function if that matters.

8. The table reference isn’t locked

A formula written as =VLOOKUP(G2,A2:D500,4,FALSE) works in the first row. Copy it down one row and the range becomes A3:D501; ten rows down it’s A12:D510. The top of the list drops out of the search, so values near the top start returning #N/A or wrong matches.

Lock the range with dollar signs so it stays put when you fill the formula down. Click inside A2:D500 in the formula bar and press F4 to add them:

=VLOOKUP(G2,$A$2:$D$500,4,FALSE)

Leave G2 relative so each row still looks up its own value. Microsoft’s VLOOKUP page recommends absolute references for exactly this reason. Turning the source data into an Excel table (Ctrl + T) is another option: a reference like Products[#Data] doesn’t shift when copied and grows as you add rows.

9. An inserted column broke col_index_num

The column number in VLOOKUP is a fixed value. If the price is column 4 and someone inserts a new column inside the range, the price moves to column 5, but the formula still asks for 4 and quietly returns whatever now sits there. Deleting a column inside the range can push the number past the edge and cause #REF!.

To make the column number follow the header, let MATCH find it. With headers in A1:D1:

=VLOOKUP(G2,$A$2:$D$500,MATCH("Price",$A$1:$D$1,0),FALSE)

Now the formula looks for the column titled Price wherever it ends up. XLOOKUP and INDEX/MATCH avoid the problem completely, because they point at the return column itself instead of counting columns.

10. Calculation is set to Manual

If VLOOKUP shows an old result after you change the lookup value or the source data, the formula may be fine and the workbook simply isn’t recalculating. Go to Formulas > Calculation Options and choose Automatic. Pressing F9 forces a one-time recalculation.

Calculation mode can change without anyone noticing, for example when a workbook saved in Manual mode is opened first in a session. Our guide to Excel formulas not calculating or updating covers that and other causes, such as formulas stored as text.

11. #SPILL! after a whole-column lookup

In current Excel, a formula such as =VLOOKUP(A:A,A:C,2,FALSE) tries to look up every cell in column A at once and spill a result for each one. Microsoft’s VLOOKUP page says to refer to a single cell such as A2, or use @A:A, to return one value per row. The #SPILL! error guide explains this case in more detail.

Whole-column ranges such as A:D also make large workbooks slower to recalculate. If many lookups make Excel freeze, see Excel not responding.

Alternatives: XLOOKUP and INDEX/MATCH

If you’re fighting VLOOKUP’s limits, a different function is often the cleaner fix.

XLOOKUP

Microsoft’s XLOOKUP function page lists it for Excel for Microsoft 365, Excel 2024 and Excel 2021, and says it isn’t available in Excel 2016 or 2019. It takes a lookup range and a return range separately, so the return column can sit on either side of the lookup column, and exact match is the default:

=XLOOKUP(G2,$A$2:$A$500,$D$2:$D$500,"Not found")

The fourth argument replaces the IFNA wrapper, and because the formula points directly at column D, inserting a column between A and D doesn’t change which values come back. Optional arguments add approximate matching (next smaller or next larger), wildcards, and searching from the last row up.

INDEX/MATCH

INDEX/MATCH works in every Excel version, including 2016 and 2019. MATCH finds the row number in the lookup column and INDEX returns the value from that row of the return column:

=INDEX($D$2:$D$500,MATCH(G2,$A$2:$A$500,0))

The 0 in MATCH means exact match. Like XLOOKUP, it can look left and isn’t affected by inserted columns. Both alternatives are still affected by text vs number mismatches and stray spaces, so the fixes in sections 3 and 4 apply to them too. For more time-saving formula habits, see our Excel tips and tricks.

Related guides

• Excel formulas not calculating or updating: how to fix it
• #SPILL! error in Excel: what it means and how to fix it
• Excel tips and tricks: 10 ways to work faster in Excel
• How to move a row in Excel (without overwriting data)
• Excel not responding: how to fix freezes and hangs

Official sources

• Microsoft Support: VLOOKUP function
• Microsoft Support: How to correct a #N/A error in the VLOOKUP function
• Microsoft Support: XLOOKUP function