"There should be data, but VLOOKUP returns #N/A..." This #N/A error is the wall that people who start using functions in Excel almost always run into.
#N/A means "Not Available". In other words, Excel is saying, "I looked for it, but I couldn't find it," but when I look at it visually, it shows up even though the value is there, so it's not a good idea.
In this article, based on information as of March 2026, we will explain in an easy-to-understand manner 5 invisible reasons for #N/A even though VLOOKUP exists and solutions using the IFERROR function and XLOOKUP function. Even if you are new to Excel, you can solve the problem by following the steps, so please give it a try.
What is #N/A error? Why VLOOKUP says "Not Found"
First, let's review the basics. VLOOKUP is a function that "searches for the search value starting from the leftmost column of the specified range and returns the value of the specified column of the found row."
The format is like this:
=VLOOKUP(search value, range, column number, search method)If this search value is not found in the leftmost column of the range, Excel returns #N/A. Roughly speaking, it's a "Can't find what you're looking for" error.
You may think, "Oh no, there is a value, right?" However, there are many cases in which Even if they look the same to the human eye, they are different to Excel. The following five are typical causes.
Cause 1: There are invisible spaces before and after the cell
This is the most common cause. Even though "A001" looks the same at first glance, there are actually half-width spaces and full-width spaces mixed in at the beginning and end of the cell.
This phenomenon tends to occur with data copied and pasted from other systems or with data imported from CSV files.
How to confirm
Double-click the cell to enter edit mode and move the cursor before or after the text. If there is a space, the cursor will move one character.
A more reliable method is to compare the number of characters using the LEN function. For example, if "A001" is supposed to have 4 characters, but =LEN(A2) returns 5, there is a space hidden somewhere.
Solution: Remove with TRIM function
TRIM function is a function that removes extra spaces before and after. Use it by incorporating it into VLOOKUP.
=VLOOKUP(TRIM(D2), A2:B100, 2, FALSE)If there is space on the side to be searched (the leftmost column of the range), you can either create a work column in advance and use =TRIM(A2) to prepare clean data, or use an array formula that is confirmed with Ctrl+Shift+Enter after entering the formula.
However, full-width spaces cannot be removed with TRIM, so use the SUBSTITUTE function together:
=VLOOKUP(SUBSTITUTE(TRIM(D2)," ",""), A2:B100, 2, FALSE)* “ ” is a double-byte space.
Cause 2: Mixing numbers and strings (data type mismatch)
This is also very true. For example, the search value cell contains the number 1001, but the searched cell contains the string "1001".
To humans, "1001" looks the same, but to Excel, The number 1001 and the string "1001" are completely different. That's why I get an error saying "not found".
How to confirm
If the cell value is left-aligned, it is a string, and if it is right-aligned, it is a number (in standard format). Also, if a green triangle mark appears at the top left of the cell, it is a sign that the number is being saved as a string.
Solution
Method A: Convert string to number
Select the cell with the green triangle mark → click the "!" icon that appears → select "Convert to number". It is OK to select multiple cells at once.
Method B: Align types using formulas
If you want to convert the search value to a string and search:
=VLOOKUP(TEXT(D2,"0"), A2:B100, 2, FALSE)Conversely, if you want to convert the search value to a number:
=VLOOKUP(D2*1, A2:B100, 2, FALSE)or using the VALUE function:
=VLOOKUP(VALUE(D2), A2:B100, 2, FALSE)Cause 3: Search method (4th argument) is TRUE or omitted
The fourth argument (search method) of VLOOKUP specifies FALSE (exact match) or TRUE (approximate match). Here's the catch.
If the fourth argument is omitted, the default is TRUE (approximate match).
Approximate matching will not work properly if the leftmost column of the range is not in ascending order. If you use TRUE with unordered data, it will return #N/A or a random value.
Solution
If you want to find pinpoint matching values such as product codes and employee numbers, be sure to set the fourth argument to FALSE.
=VLOOKUP(D2, A2:B100, 2, FALSE)It is OK to write "0" instead of "FALSE". By the way, it is not uncommon for people who have been using Excel for many years to omit this part and get hooked.
Cause 4: Search range is off (wrong column number/reference range)
This is a pattern where the reference range shifts when copying a formula. For example, if you copy =VLOOKUP(D2, A2:B100, 2, FALSE) below, A2:B100 will automatically change to A3:B101.
If the range shifts, the search value will be out of range and #N/A will be displayed.
Solution: Make it an absolute reference
Let's fix the range with $ mark (absolute reference).
=VLOOKUP(D2, :00, 2, FALSE)If you press F4 key with A2:B100 selected, it will be automatically converted to :00.
Also, be careful not to specify a column number incorrectly. If the range is only two columns, A:B, but the column number is 3, a #REF! error will occur (this is a different error from #N/A, but it is easy to confuse it).
Cause 5: Line feed code or unprintable characters are mixed in
Data copied from CSV or the web may contain line feed codes (Alt+Enter) and control characters. This also cannot be removed using the TRIM function.
Solution: Remove with CLEAN function
CLEAN function is a function that deletes unprintable control characters. It is the strongest when combined with the TRIM function.
=VLOOKUP(CLEAN(TRIM(D2)), A2:B100, 2, FALSE)If the data being searched is also dirty, create a work column and clean it with =CLEAN(TRIM(A2)) before applying VLOOKUP.
I don't want the error to be displayed in the first place! How to delete #N/A with IFERROR function
It's best to find the cause and fix it, but there are cases where you want to leave it blank if it doesn't apply, or you don't want to stop working even if an error occurs.
In such a case, wrap the VLOOKUP with the IFERROR function.
=IFERROR(VLOOKUP(D2, :00, 2, FALSE), "")Enter the value you want to display in the event of an error in the second argument. If it is blank, write "", if you want to display a character, write "Not applicable".
However, there is one thing to note. IFERROR also hides all errors other than #N/A (such as #REF! and #VALUE!). In other words, there is a danger that even bugs in the formula may become invisible.
If you want to catch only #N/A, use the IFNA function (Microsoft official).
=IFNA(VLOOKUP(D2, :00, 2, FALSE), "Not applicable")In this way, "Not Applicable" will be displayed only when #N/A, and real bugs such as #REF! will be displayed as is, so you can rest assured.
No more worrying about #N/A! Option to switch to XLOOKUP
If you are using Excel 2021 or later or Microsoft 365, we recommend switching to the XLOOKUP function (Microsoft official).
XLOOKUP has improved the bad points of VLOOKUP:
- Built-in error handling: You can specify a "value if not found" in the 4th argument. No need for IFERROR
- It is OK even if the search column is not the leftmost column: VLOOKUP can only search the leftmost column of the range, but XLOOKUP is free
- No need to specify column numbers: The columns to be returned are directly specified, so there is no misalignment when adding or deleting columns
- Default is exact match: Omitting the 4th argument will not result in an approximate match
The format is like this:
=XLOOKUP(search value, search range, return range, "Not found")For example:
=XLOOKUP(D2, A2:A100, B2:B100, "Not applicable")When written with VLOOKUP, =IFERROR(VLOOKUP(D2,:00,2,FALSE),"Not applicable"), but with XLOOKUP, it can be written simply in one line. As of March 2026, Microsoft 365 supports XLOOKUP, so if you can use it, be sure to switch to it.
Details on how to use XLOOKUP and the differences between it and the old functions are also explained in XLOOKUP vs VLOOKUP thorough comparison guide.
FAQ
What is the difference between #N/A and #REF! in VLOOKUP?
#N/A is a "search value not found" error, and #REF! is a "reference is invalid (for example, column number is out of range)" error. Since the causes are completely different, let's look at the type of error and switch the solution.
Which should I use: IFERROR or IFNA?
For #N/A countermeasures in VLOOKUP, IFNA function is recommended. Since IFERROR hides all errors, there is a risk of missing formula bugs (#REF! and #VALUE!). IFNA is safe because it only catches #N/A and displays other errors.
Can I use XLOOKUP in Excel 2019?
Unfortunately, XLOOKUP is available for Excel 2021 and later or Microsoft 365. It cannot be used in Excel 2019 or earlier versions, so please use IFERROR or IFNA to augment VLOOKUP.
Can double-byte spaces be removed using the TRIM function?
No. The TRIM function removes half-width spaces only. To remove double-width spaces, use the SUBSTITUTE function together as in =SUBSTITUTE(TRIM(A2)," ","") ("" is a double-width space).
Is there any advantage to using INDEX+MATCH instead of VLOOKUP?
Yes. INDEX+MATCH works even if the search column is not at the left end, and the advantage is that it does not shift easily even when inserting or deleting columns. However, as of 2026, XLOOKUP has the same or higher functionality, so if you have an environment where XLOOKUP can be used, it is simpler to prioritize that.
