VLOOKUP Not Working? Fix #N/A Errors by Cause (2026)

What does it mean when VLOOKUP is not working?

When VLOOKUP is not working, it is almost always one of three things: the fourth argument is not FALSE, the lookup value and the table key are different data types, or hidden spaces make two matching-looking values unequal. #N/A means “not available”: Excel searched the first column of your table and did not find the value.

Here is the syntax every example in this post builds on:

=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
=VLOOKUP(A2, $F$2:$H$100, 2, FALSE)

Microsoft documents the four arguments on its VLOOKUP function support page. In my experience, resist the urge to rewrite the formula from scratch. Work down a checklist instead, because the formula is usually fine and the data is the problem.

Order	Check	Symptom	Quick fix
1	Fourth argument	#N/A or a wrong value on data that exists	Enter FALSE or 0
2	Number vs text	Values look identical but do not match	VALUE() or &"" to align types
3	Extra spaces, hidden characters	Only some rows fail	TRIM, CLEAN, SUBSTITUTE
4	First column of the range	Key sits to the right of the range start	Start the range at the key column
5	Column index	Wrong column returned, or #REF!	Recount inside the range
6	Absolute references	Early rows work, later rows fail	$F$2:$H$100
7	Merged cells, spill	Only the first row works, or #SPILL!	Unmerge, clear blocking cells

Why does VLOOKUP return #N/A when the value exists?

VLOOKUP returns #N/A when the value you gave it is not byte-for-byte identical to anything in the first column of the table, even if your eyes say otherwise. Excel compares data type and every character, including spaces you cannot see.

Microsoft’s guide to correcting a #N/A error lists the same suspects: the value genuinely is not in the source data, approximate match is on, the lookup item is a number while the table holds text, and stray spaces. The page was summarized from search results when I wrote this, so confirm the current wording on Microsoft’s site.

The fastest way to separate “truly missing” from “present but unmatched” is to pick one failing row and press Ctrl+F in the table to search for it. If Find locates the value but VLOOKUP cannot, you have a formatting problem. If Find also fails, the value really is absent, and the formula is behaving correctly. In that case fill the gap in the data, or show a friendly message with IFNA.

What happens if I leave out the fourth argument?

If you omit the fourth argument, or set it to TRUE, VLOOKUP does an approximate match: it finds the largest value that is less than or equal to your lookup value. That mode assumes the first column is sorted ascending, and on unsorted data it can return the wrong row without any warning.

This is the nastiest VLOOKUP trap, because a plain #N/A at least announces itself. A wrong-but-plausible answer travels into reports. Take this small unsorted table:

Employee ID (A)	Name (B)
1001	Alvarez
1005	Brooks
1003	Chen

=VLOOKUP(1003, A2:B4, 2) uses binary search. It checks the middle row, sees 1005 is too big, steps up, and can land on Alvarez or give #N/A rather than Chen. Change it to =VLOOKUP(1003, A2:B4, 2, FALSE) and Excel compares row by row from the top, returning Chen.

Approximate match has a legitimate home: lookup bands. A tax-bracket table, a commission schedule, or letter grades by score range all rely on “find the closest lower threshold.” For those, sort the first column ascending and use TRUE on purpose. For IDs, SKUs, names, and email addresses, the answer is always FALSE.

Why does VLOOKUP fail when one value is text and the other is a number?

Excel treats the number 1001 and the text “1001” as different values, so VLOOKUP returns #N/A even though both cells display the same four characters. This mismatch is common in data exported from accounting systems, CRMs, or CSV files, especially for ZIP codes, SKUs, and ID columns.

Signs that a number is stored as text:

• A small green triangle in the corner of the cell
• Left-aligned digits in a column that should be right-aligned
• =ISNUMBER(A2) returns FALSE
• SUM on the column returns 0 or a figure that is too low

Fix the type on the lookup side if you would rather not edit the source table:

Situation	Formula	What it does
Lookup value is text, table keys are numbers	=VLOOKUP(VALUE(A2),$F$2:$H$100,2,FALSE)	Converts the lookup value to a number
Lookup value is a number, table keys are text	=VLOOKUP(A2&"",$F$2:$H$100,2,FALSE)	Converts the lookup value to text
Types are mixed, unknown which	=IFERROR(VLOOKUP(A2,$F$2:$H$100,2,FALSE),VLOOKUP(A2&"",$F$2:$H$100,2,FALSE))	Tries as-is, then retries as text

To convert a whole column of text-numbers in place, type 1 in an empty cell and copy it. Select the stubborn range, choose Paste Special, then Multiply. Another route is Data, Text to Columns, then Finish. Save a copy of the workbook before converting anything.

How do trailing spaces break a VLOOKUP?

A trailing space makes “Chen” and “Chen ” two different strings, so VLOOKUP cannot match them. Leading spaces, doubled internal spaces, line breaks, and non-breaking spaces copied from web pages hide inside imported data all the time.

Diagnose first. In an empty cell compare the two lengths:

=LEN(A2)
=LEN(TRIM(A2))

If they differ, spaces are present. The standard fix uses the TRIM function on the lookup value:

=VLOOKUP(TRIM(A2), $F$2:$H$100, 2, FALSE)

If TRIM changes nothing and the lengths still look wrong, suspect a non-breaking space, character code 160, which TRIM ignores. Replace it first:

=VLOOKUP(TRIM(SUBSTITUTE(A2, CHAR(160), " ")), $F$2:$H$100, 2, FALSE)

CLEAN removes line breaks and other non-printing characters. One catch: cleaning only the lookup value cannot help if the spaces live in the table’s key column. In that case add a helper column with TRIM on the keys, copy it, paste as values over the original, and delete the helper. Cleaning both sides is the only version that stays fixed.

How do I set the column index number and table range correctly?

The column index counts columns within the range you selected, with the leftmost column of the range as 1. VLOOKUP also searches only that leftmost column, so your key must be the first column of the range or the formula fails from the start.

Range	Column 1	Column 2	Column 3
F2:H100	F	G	H
B2:E100	B	C	D
D2:H100	D	E	F

Two mistakes cause most of the trouble here.

The first is a key that is not in the range’s first column. If employee IDs are in column C but your range is A2:D100, VLOOKUP searches column A for them and finds nothing. Shrink the range to C2:D100 and recount the index.

The second is a hardcoded index that goes stale. Insert a column and the 2 you typed now points at the wrong field. Let MATCH find the column by header name instead:

=VLOOKUP(A2, $F$1:$K$100, MATCH("Unit Price", $F$1:$K$1, 0), FALSE)

An index larger than the range width returns #REF!, and an index below 1 returns #VALUE!. If you see one of those codes instead of #N/A, look at the number, not the lookup value.

Why does VLOOKUP work in row 2 but fail further down?

You almost certainly forgot the dollar signs. Without absolute references, the table range shifts one row each time the formula is copied down, so the top rows of the table fall out of the search area and later lookups return #N/A.

Here is a failure case. This is a composite of a very common pattern, not one specific file.

Row	Formula as copied	Effective range	Result
2	=VLOOKUP(A2,F2:G10,2,FALSE)	F2:G10	Works
3	=VLOOKUP(A3,F3:G11,2,FALSE)	F3:G11	First table row missing
4	=VLOOKUP(A4,F4:G12,2,FALSE)	F4:G12	First two table rows missing

Anything that lives in row 2 of the table cannot be found from row 3 onward. The repair takes two seconds:

=VLOOKUP(A2, $F$2:$G$10, 2, FALSE)

Click inside the range in the formula bar and press F4 to add the dollar signs, or use Ctrl+T to turn the table into an Excel Table, whose structured references grow with the data. If your table keeps growing, avoid a hard-coded last row such as 10. Use whole columns like $F:$G or an Excel Table instead.

Can merged cells or a #SPILL! error also cause lookup problems?

Yes. A merged cell stores its value only in the top-left cell, and the rest of the merged block is empty. When a lookup column is built from merged region names or departments, the first row matches and the others return #N/A. Unmerge the cells, select the range, use Go To Special, then Blanks, type = followed by an up-arrow, and press Ctrl+Enter to fill each blank from above. Paste as values afterward.

#SPILL! comes from dynamic arrays in Microsoft 365 and Excel 2021 or later. If the lookup value is a multi-cell range, results try to spill into the cells below, and any non-empty cell in the way produces #SPILL!. Clear the blocking cells or point the lookup at a single cell such as A2. Older Excel versions behave differently with the same formula, so check the version of whoever opens the file.

Is XLOOKUP a better choice than VLOOKUP?

For most new work, yes, if everyone who opens the file has Microsoft 365 or Excel 2021 or later. XLOOKUP defaults to an exact match, takes a return range instead of a column number, and can return values from the left of the key column. You can read the syntax on Microsoft’s XLOOKUP page.

=XLOOKUP(A2, $F$2:$F$100, $G$2:$G$100, "Not found")

The fourth argument is what to show when nothing matches, so no IFERROR wrapper is required.

Feature	VLOOKUP	XLOOKUP
Default match	Approximate (risky)	Exact
How the return column is chosen	Column number	A return range
Return from left of the key	No	Yes
When nothing matches	#N/A (needs IFERROR/IFNA)	Built-in fallback value
After inserting a column	Index can go stale	Unaffected
Availability	All versions	Microsoft 365, Excel 2021 and later

Mind the compatibility gap. Excel 2016 and 2019 have no XLOOKUP, so the workbook shows #NAME? for whoever opens it there. The model of Excel on your desk is not the whole story. If a client, auditor, or coworker on an older install gets the file, stay with VLOOKUP and FALSE, or use INDEX with MATCH. XLOOKUP does not magically fix text-versus-number or hidden-space problems either, so TRIM and type alignment still matter.

What is a quick diagnostic routine when nothing works?

Pick one failing row and run these checks in empty cells. Each one isolates a single cause.

Check	Formula	What you want to see
Is the lookup value a number?	=ISNUMBER(A2)	Same answer as the table key
Is the table key a number?	=ISNUMBER(F2)	Same answer as above
Any extra spaces?	=LEN(A2)=LEN(TRIM(A2))	TRUE
Does the key exist in the table?	=COUNTIF($F$2:$F$100,A2)	1 or more
Are the cells truly equal?	=A2=F2	TRUE

If the two ISNUMBER results differ, you have a type mismatch. If the LEN test is FALSE, you have spaces. If COUNTIF returns 0 while the eyeball test says they match, a hidden character is the likely cause. If everything checks out, Formulas, Evaluate Formula steps through the calculation one piece at a time.

Do not confuse these symptoms with a workbook that is simply not recalculating. If your formulas show as text or do not update at all, the problem is not VLOOKUP, and my guide to fixing an Excel formula that is not working covers calculation mode and cell formatting.

For a wider tour of lookup, logical, and text functions, the Excel functions cheat sheet puts them side by side, and the 10 Excel formulas everyone should know is a good foundation if you are newer to spreadsheets. If a different calculation is giving you trouble, there is a separate walkthrough on subtracting in Excel.

A few habits make everything above faster. F4 for absolute references and Ctrl+Enter for filling blanks both appear in the 50 keyboard shortcuts that save hours. If large workbooks are making Excel itself freeze, work through fixing a slow computer before blaming the formula. And once the lookups behave, the AI productivity tools guide shows ways to automate the repetitive cleanup that causes these errors in the first place.

This article is general information written with US users and Microsoft 365 in mind. Menu names and behavior vary by Excel version, platform, and region settings, and XLOOKUP requires Microsoft 365 or Excel 2021 or later. Save a copy of any important workbook before changing it, and confirm current behavior in Microsoft’s official support documentation.