Excel 中 VLOOKUP 顯示 #N/A、COUNTIF 卻顯示值存在：實測 18 組相似值
VLOOKUP says #N/A, COUNTIF says the value is there: 18 look-alike pairs tested in a real Excel
文章在 Microsoft 365 build 16.0.20430 中測試 18 組相似值，對照 VLOOKUP、XLOOKUP、MATCH、= 和 COUNTIF 的結果。
You can see the customer number in both sheets. VLOOKUP returns #N/A. You wrap the value in TRIM, as every forum thread tells you to, and it still returns #N/A.
I wanted to know which invisible differences really break a lookup, so I stopped guessing and asked Excel. I put 18 pairs of look-alike values into a workbook, one pair per row, and had a hidden copy of Excel (Microsoft 365, build 16.0.20430) calculate VLOOKUP, XLOOKUP, MATCH, the = operator and COUNTIF on each pair. The last two columns run VLOOKUP again after cleaning the left value with TRIM, then with TRIM and CLEAN.
1 means Excel found the match. 0 means #N/A or FALSE.
| Left value vs right value | VLOOKUP | XLOOKUP | MATCH | = |
COUNTIF | TRIM | TRIM + CLEAN |
|---|---|---|---|---|---|---|---|
Trailing space: Smith vs Smith
|
0 | 0 | 0 | 0 | 0 | 1 | 1 |
Double space: John Smith vs John Smith
|
0 | 0 | 0 | 0 | 0 | 1 | 1 |
| Tab between the words | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Line break inside the cell | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Non-breaking space (code 160) | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Narrow no-break space (U+202F) | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Zero-width space at the end (U+200B) | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Left-to-right mark at the start (U+200E) | 0 | 1 | 0 | 1 | 1 | 0 | 0 |
| Right-to-left mark before a number stored as text | 0 | 1 | 0 | 1 | 1 | 0 | 0 |
| Byte-order mark at the start (U+FEFF) | 0 | 1 | 0 | 1 | 1 | 0 | 0 |
| Soft hyphen inside the word (U+00AD) | 0 | 1 | 0 | 1 | 1 | 0 | 0 |
Text "12345" vs the number 12345 |
0 | 0 | 0 | 0 | 1 | 0 | 0 |
Text "012345678" vs the number 12345678 |
0 | 0 | 0 | 0 | 1 | 0 | 0 |
| Curly apostrophe (U+2019) vs straight, in O'Brien | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Dash (U+2013) vs hyphen, in A-100 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
ACME vs acme
|
1 | 1 | 1 | 1 | 1 | 1 | 1 |
| Hebrew name with a left-to-right mark | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| Hebrew name with a non-breaking space | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
Fifteen of those rows are real differences. VLOOKUP found none of them. Three results surprised me.
1. TRIM fixed 2 of the 15
Trailing spaces and double spaces. That's it. The non-breaking space, which tends to arrive with text pasted from a web page or a system export, survives TRIM untouched.
CLEAN didn't rescue the tab or the line break either. It deletes those characters instead of turning them into a space, so John + line break + Smith becomes JohnSmith, and that still misses John Smith.
2. XLOOKUP is not just VLOOKUP with nicer syntax
XLOOKUP ignored four invisible marks that VLOOKUP and MATCH treat as real characters: the left-to-right mark, the right-to-left mark, the byte-order mark and the soft hyphen. The = operator and COUNTIF ignored them too.
So two sheets that look up the same data, one with VLOOKUP and one with XLOOKUP, can report a different number of matches. Neither one is broken. They compare text differently.
3. COUNTIF says yes when every lookup says no
COUNTIF read the text "012345678" as the number 12345678 and counted it. Every lookup function returned #N/A for the same pair.
That explains a classic support question: "COUNTIF says the value is in the list, so why does VLOOKUP fail?" COUNTIF converts text that looks like a number. The lookups don't.
One more I didn't expect. In the two Hebrew rows, every function ignored the direction mark and the non-breaking space. In English text, the same characters broke VLOOKUP. Excel's comparison depends on the script the text is written in.
Find the hidden character yourself
In Microsoft 365 this formula lists the code of every character in A2:
=TEXTJOIN(" ",TRUE,UNICODE(MID(A2,SEQUENCE(LEN(A2)),1)))
Smith with a zero-width space at the end comes back as 83 109 105 116 104 8203. The usual suspects:
- 160: non-breaking space
- 8203: zero-width space
- 8206 and 8207: direction marks
- 8217: curly apostrophe
- 65279: byte-order mark
Why I measured instead of trusting the forums
This started with a match checker I built for people who live in Excel. It looks up each value the way VLOOKUP does and, when Excel would miss it, says why: extra spaces, a number stored as text, a look-alike dash or quote, a hidden character, a typo. It changes nothing until you approve a fix, and the fix goes into a new copy of the file. The original is never written.
The part I'd reuse in any spreadsheet tool: when Excel is on the machine, the checker asks a hidden, separate Excel to confirm each finding, so Excel has the last word instead of my rules. The table above came from the same trick. Write the pairs to a workbook, let a hidden Excel calculate, read the results back.
Which of these has cost you the most time?
Disclosure: Claude, an AI assistant, wrote this article and ran the measurement in Excel on my machine. The 1s and 0s in the table are Excel's own output.
來源：DEV Community · dev.to