Have you ever used VLOOKUP in Excel and thought, “Wait, this value is definitely correct, so why am I getting a #N/A error?”
This is actually an extremely common Excel problem. Two values can look exactly the same on the screen, but Excel may still treat them as completely different things. In most cases, there’s some “invisible” issue hiding in the data.
In this article, based on information current as of March 2026, we’ll explain five common reasons values don’t match in VLOOKUP or XLOOKUP, along with specific fixes for each one. If you’ve already spent hours stuck on this, try the steps from the top down.
Cause 1: Invisible Spaces Before or After the Cell Value
This is the most common cause. Data copied from another system or a web page often includes half-width spaces at the beginning or end of a cell.
Visually, you can’t tell the difference between “Tokyo” and “Tokyo ” with a trailing space. But Excel is very literal and treats them as different text strings.
How to check: Use the LEN function. For example, enter =LEN(A2) and compare the character counts for the lookup value and the value in the lookup table. If the counts are different, one of the cells contains an extra character.
Fix: Use the TRIM function to remove leading and trailing spaces.
=VLOOKUP(TRIM(A2), B:D, 3, FALSE)In simple terms, TRIM removes unnecessary spaces stuck to the beginning or end of a cell. Just wrap the first argument of VLOOKUP, the lookup value, in TRIM.
Cause 2: Hidden Line Breaks or Control Characters
A cell may contain a line break, such as one entered with Alt+Enter, or an invisible control character. This often happens with CSV files or data exported from business systems.
In Excel, line breaks exist as CHAR(10) (LF) or CHAR(13) (CR), and TRIM alone won’t remove them.
Fix: Use the CLEAN function. CLEAN removes non-printing control characters all at once.
=VLOOKUP(TRIM(CLEAN(A2)), B:D, 3, FALSE)The standard approach is to combine TRIM and CLEAN. A good rule of thumb: use CLEAN first to remove control characters, then TRIM to remove spaces.
Cause 3: A Nonbreaking Space from Web Copy and Paste
When you copy and paste data from a web page, you may bring in a special blank character called a nonbreaking space (CHAR(160)) instead of a normal half-width space.
The annoying part is that TRIM and CLEAN don’t remove this character. It looks exactly like a regular space, so if you’ve already tried TRIM and the values still won’t match, this is a prime suspect.
Fix: Use the SUBSTITUTE function to replace CHAR(160) with a regular space, then apply TRIM.
=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))This is a general-purpose cleaning formula. It handles most invisible-character issues in three steps: convert nonbreaking spaces to regular spaces, remove control characters, then remove extra spaces.
If you also want to remove full-width spaces, use this version:
=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2, CHAR(160), " "), " ", " ")))Cause 4: One Value Is a Number and the Other Is Text
Two cells can both appear to contain “12345,” but one may be stored as a number and the other as text. This is another classic reason VLOOKUP won’t find a match.
In Excel, text is usually left-aligned and numbers are usually right-aligned. If you see a green triangle in the upper-left corner of a cell, that’s an error indicator telling you the number is stored as text.
How to check: Use =ISTEXT(A2) or =ISNUMBER(A2). If the lookup value and the lookup table return different results, you have a data type mismatch.
Fix, text to number:
=VLOOKUP(VALUE(A2), B:D, 3, FALSE)The VALUE function converts numbers stored as text into actual numbers. If you want to convert the number side to text instead, use =TEXT(A2, "0").
Bulk fix: Select the cells stored as text, click the green triangle, and choose “Convert to Number.” Microsoft’s official help page also walks through the steps.
Cause 5: You Left Out VLOOKUP’s Fourth Argument
This one is easy to miss. If you omit VLOOKUP’s fourth argument, the match type, Excel defaults to TRUE, or approximate match.
If you want an exact match, always specify FALSE.
× =VLOOKUP(A2, B:D, 3) ← 近似一致（予期しない結果になりがち）
○ =VLOOKUP(A2, B:D, 3, FALSE) ← 完全一致（こちらが安全）In approximate match mode, the lookup range must be sorted in ascending order or Excel may return the wrong result. If you’re getting an unexpected value instead of #N/A, check this first.
By the way, the XLOOKUP function, available in Microsoft 365 and Excel 2021 or later, defaults to exact match, so this specific problem doesn’t happen. If you can use it, switching to XLOOKUP is often worth it. Microsoft’s official XLOOKUP guide is a helpful reference.
Summary: A Handy All-Purpose Cleaning Formula
We’ve covered five possible causes, but honestly, it’s often hard to tell which one is responsible. When you’re not sure, try using this all-purpose cleaning formula as the lookup value in VLOOKUP.
=VLOOKUP(TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2, CHAR(160), " "), " ", " "))), B:D, 3, FALSE)This one formula handles spaces, control characters, nonbreaking spaces, and full-width spaces. If it still doesn’t work, suspect the type mismatch from Cause 4 and check with VALUE, ISTEXT, or ISNUMBER.
Also remember that the lookup table, the range specified as VLOOKUP’s second argument, may have the same problem. If cleaning only the lookup value doesn’t solve it, the reliable fix is to clean the lookup table data in a separate column and point VLOOKUP to that cleaned version.
FAQ
Does #N/A in VLOOKUP always mean there are invisible characters?
No. #N/A can also appear when the lookup value simply doesn’t exist in the lookup range, or when the lookup range is specified incorrectly. First, make sure the value you’re searching for is really in the first column of the lookup range. To check whether invisible characters are involved, compare character counts with the LEN function.
What’s the difference between TRIM and CLEAN?
TRIM removes extra spaces, while CLEAN removes non-printing control characters, such as line breaks. If you’re not sure which one you need, it’s usually safest to use both together, like =TRIM(CLEAN(セル)).
Should I switch to XLOOKUP?
If you’re using Microsoft 365 or Excel 2021 or later, XLOOKUP is a good choice. It defaults to exact match, the lookup column doesn’t have to be on the far left, and you can define the return value for errors inside the function. Just be careful with compatibility if you share files with people using Excel 2016 or 2019.
I opened a CSV file and the numbers became text. How can I fix them all at once?
The easiest way is to select the affected range, click the green triangle error indicator in the upper-left corner, and choose “Convert to Number.” For large datasets, you can also type “1” in an empty cell, copy it, select the target range, then use Paste Special and choose Multiply to convert everything in bulk.
What is CHAR(160), and how is it different from a regular space?
CHAR(160) is a special blank character called a nonbreaking space. In HTML, it’s used as , and it can sneak in when you copy text from a web page. Because it has a different character code from a regular space, CHAR(32), Excel’s TRIM function won’t remove it. You need to replace it with SUBSTITUTE.
