VLOOKUP Returns #N/A? Find and Fix the Cause

When VLOOKUP returns #N/A, Excel has not found the lookup value in the first column of the range it searched. Check that the value exists, that its type and contents match the source data, and that the formula uses the intended lookup range and match mode; then test the corrected formula before hiding the error.

What #N/A means in VLOOKUP

VLOOKUP searches down the leftmost column of a table array for a lookup value, then returns a value from another column in the same row. #N/A means the lookup did not find a match under the formula’s search rules. It is different from errors such as #REF!, which can indicate an invalid column number or deleted reference.

The basic syntax is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). For example, =VLOOKUP(E2,A2:C100,3,FALSE) searches for the value in E2 in column A and returns the corresponding value from column C. With FALSE, it requests an exact match. If that value is absent, Excel returns #N/A.

Start by clicking the cell with the error and checking the formula bar. A value that looks correct on screen may differ from the source because it is stored as text, contains an invisible space, or is searched in the wrong column.

Check the lookup value and search column

VLOOKUP can search only in the first column of table_array. If your lookup key is in column C but the selected range starts at A, VLOOKUP searches A—not C. Adjust the range so its first column contains the keys, or use a function such as XLOOKUP where available.

Verify these points before changing data:

• Does the lookup value appear in the first column of the selected range?
• Is the entire relevant dataset included, including newly added rows?
• Does the formula point to the intended worksheet and cells?
• Are there duplicates? VLOOKUP returns the first matching row, which can produce an unexpected result even without #N/A.

Use Find (Ctrl+F on Windows or Command+F on Mac) to look for the value in the search column. If the value is on another sheet, check that the formula references that sheet and the correct range.

Match the data type and clean the text

A number and a number stored as text can look identical but fail to match. This often happens with imported data, product codes, or identifiers. Check a sample cell with ISTEXT or ISNUMBER, or compare the values in helper cells. Convert data consistently rather than changing only one side of the lookup.

Text can also contain leading or trailing spaces, nonbreaking spaces copied from websites, or other hidden characters. A useful cleanup approach is to create a helper column. For ordinary spaces, =TRIM(A2) removes leading and trailing spaces and reduces repeated internal spaces. For nonbreaking spaces, a formula such as =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) can replace that character with a regular space before trimming. Apply comparable cleanup to both the lookup key and the source keys.

Do not automatically remove spaces from every identifier: spaces may be meaningful in some codes. Keep original data in place, work in a helper column, and confirm that cleanup preserves the intended keys.

Check exact and approximate match settings

The fourth VLOOKUP argument controls matching. Use FALSE (or 0) when the key must match exactly. Although omitting this argument selects approximate matching, leaving it out can cause confusing results. Make the setting explicit.

With approximate matching, VLOOKUP expects the first column to be sorted in ascending order. It returns the closest lower value when it cannot find an exact one; depending on the data and formula, an unsorted range may return an incorrect result. Approximate matching is useful for sorted thresholds, such as grade bands or quantity breaks, but is usually not the right choice for names, IDs, or product codes. If an approximate lookup value is below the smallest entry, #N/A can result.

For a standard exact lookup, prefer a formula such as =VLOOKUP(E2,$A$2:$C$100,3,FALSE). The dollar signs keep the lookup range fixed if you copy the formula down.

Verify the range and column number

The third argument, col_index_num, counts columns from the left edge of the selected range—not from the worksheet’s column A. In A2:C100, column A is 1, B is 2, and C is 3. A wrong column number more commonly causes #REF! or returns the wrong field, but correcting it is still important when diagnosing the formula.

Ranges can also shift when formulas are copied. If one formula works and the next does not, inspect whether the table array moved. Use absolute references such as $A$2:$C$100, or convert the data to an Excel Table and use structured references. For a table that grows, a table reference can be easier to maintain than a fixed range.

When debugging, test the lookup independently: copy the value into a temporary cell, search the first column, and compare the actual cell contents. You can also evaluate parts of a formula using Excel’s formula evaluation tools, where available.

Handle an expected missing result

Sometimes #N/A is a valid signal: the key has no matching record yet. If a blank or message is more useful for readers, wrap the formula in IFNA, for example =IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found"). To show a blank, use =IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"").

Use IFNA only after checking that the formula is correct. It hides #N/A specifically, while allowing other errors to remain visible. IFERROR catches a wider range of errors, potentially concealing problems such as a broken reference or an invalid formula. During troubleshooting, leaving errors visible is often more informative.

When to use XLOOKUP or another approach

VLOOKUP remains useful in older workbooks and for simple tables whose key is the leftmost column. In versions of Excel that support it, XLOOKUP can search one range and return from another, including a column to the left. It also lets you specify a not-found result directly, for example: =XLOOKUP(E2,A2:A100,C2:C100,"Not found").

INDEX and MATCH can also separate the lookup column from the return column, which helps when VLOOKUP’s leftmost-column requirement is inconvenient. Before replacing a formula, consider whether collaborators use spreadsheet software or Excel versions that support the chosen function. For compatibility, VLOOKUP may be the practical choice in older files.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is visibly there?
Check for text-versus-number differences, extra spaces, nonbreaking spaces, a lookup range that starts in the wrong column, or a formula referencing the wrong sheet or rows. Matching appearance does not guarantee identical cell contents.

Should I use TRUE or FALSE in VLOOKUP?
Use FALSE for an exact match, which is appropriate for most names, codes, and IDs. TRUE requests approximate matching and requires the first column to be sorted in ascending order.

Can VLOOKUP search to the left?
No. It searches the first column of its table array and returns a value from a column to its right. Use XLOOKUP, where supported, or INDEX and MATCH when the return column is to the left.

How do I show blank instead of #N/A?
Wrap the formula in IFNA, such as =IFNA(VLOOKUP(E2,A2:C100,3,FALSE),""). This is best when a missing match is expected; otherwise, keep the error visible until you have corrected the cause.

Why did the formula start returning #N/A after copying it?
The table range may have shifted, or the copied formula may now refer to a different lookup cell. Lock a fixed range with dollar signs, for example $A$2:$C$100, and confirm each row’s lookup value is correct.

Related

• How to Cancel Prime Video Without Losing Track
• Merge Videos Together: Simple Ways to Join Clips
• Neural Networks: What Did They Just Hack?