vlookup #N/A error

[pkaur]

Some of the values comes up with #N/A error as a result when I m using vlookup. I can see that the values exist, but it only comes up when I actually type the value in the source column. The format matches, there are no hidden spaces or anything. what can be the reason?

 

[Cubist]

Just a guess but there might be an empty string with no space i.e. UNICHAR(8203). Notice A1 looks like a continuous " ABCXYZ " string, but there's an empty string in the middle. It looks identical to B1 but has different lengths. The usual TRIM and CLEAN don't remove those.

Book1
	A	B	C	D	E
1	ABCXYZ	ABCXYZ		7	6
Sheet3

Cell Formulas
Range	Formula
A1	A1	="ABC" & UNICHAR(8203) & "XYZ"
B1	B1	=SUBSTITUTE(A1,UNICHAR(8203),"")
D1:E1	D1	=LEN(A1:B1)
Dynamic array formulas.

 

[pkaur]

i checked length of each cell by using LEN function. its showing that each cell has 5 digits

 

[Joe4]

Welcome to the Board!

Can you show us exactly what one of these values that is erroring looks like?
And show us your VLOOKUP formula?

Also, confirm that they REALLY are equal.
Let's say one is in cell A2 and the match is in cell Z10. Then enter this formula in any blank cell:

=A2=Z10

If it returns TRUE, then Excel sees the values really are the same, which would suggest the issue is with your formula.
If it returns FALSE, then the value are not the same, so you probably have a data issue.

 

[pkaur]

It wasn't the same. something might be hidden. it worked now. Thanks.

 

[Joe4]

It wasn't the same. something might be hidden. it worked now. Thanks.

That is what I figured!