VLOOKUP says #N/A, COUNTIF says the value is there: 18 look-alike pairs tested in a real Excel

Noam Brand — Sun, 18 Oct 2026 06:19:09 +0000

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.

I stopped pasting VBA into a chatbot and pointed the agent at the actual workbook

Noam Brand — Mon, 14 Sep 2026 15:03:51 +0000

Here is the loop most people are stuck in when they use AI to fix a macro. Open the VBA editor, copy the sub out, paste it into a chat window, describe the problem, paste the answer back, run it, watch it throw error 1004 on some object it never saw, copy the error back, and go again. It works, sort of, but you are the integration layer. You are the clipboard, the compiler, and the test harness, doing it all by hand.

That loop is worse for VBA than for almost any other language, and the reason is specific.

The chatbot is guessing in the dark

Most code lives in files a model can read. VBA lives inside the workbook. Its behavior depends on the object model, the references that are enabled, the names on the sheets, and the actual state of the Excel instance that is open right now. When you paste one sub into a chat window, you have thrown all of that away. The model writes something that looks right, but it can't see the other module that already declared that variable, and it can't run the macro to find out that the range you handed it is one row short.

So it guesses. Sometimes the guess is good. Often it compiles and then does the wrong thing quietly, which is the worst outcome, because now you are debugging code you didn't write against data the model never saw.

What changes when the agent can actually run it

The fix is not a smarter chatbot. It is closing the loop, letting the thing that writes the code also read your real modules and run them.

Point an agent at the project itself: the .bas and .cls files, and the workbook that is open. You describe the change. It reads your actual modules, writes the code, runs the macro against the live workbook, and reads whatever comes back. If it throws, it sees the real runtime error, on your machine, against your data, and takes another pass before it ever hands the code to you. You review a result and a diff instead of playing clipboard.

The part that still gets me is watching it run the macro and react. Not "here is a macro that should work." It runs, it sees row 1,001 is blank, it fixes the bound, it runs again. That is the difference between an assistant that talks about your code and one that works on it.

Why this matters if you own a pile of VBA

Most serious VBA is old. Someone built a 4,000-line workbook years ago, they left, and now it is load-bearing and nobody wants to touch it. That is exactly the code where the paste-into-a-chatbot approach falls apart, because the context that matters is spread across modules and sheets you can't paste. An agent that reads the whole project and tests against the real file is the only version of "AI help" that survives contact with that kind of workbook.

Full disclosure: I built a tool for this, called VBridge. It runs Claude Code against your actual Excel VBA project on your own machine. It is not the only way to close the loop, but closing the loop is the point, so even if you wire up your own, do that part.

A two-minute before and after is here: vbridge.netlify.app

If you write VBA, or you inherited a workbook nobody wants to own, try describing a change and letting the agent run it against the real file. Going back to the chat window afterward feels like going back to hand-copying error messages, because that is what it is.

DEV Community: Noam Brand