2 cells are equal / exact, but fail to return match on vlook or index match

[ESACAWIP]

I have 2 cells (very large with lots of like breaks) which contain the same text (demonstrated by C10, A1=A2). However, if you take a look at the vlookup (c11), it fails to return a match. I tried trim / clean formula on both A1 and A2 but I still get no matches. Why is this happening?

Note: If I delete a random row from both A1 and A2 at the same spot, the vlookup will work.. but I need to preserve all line breaks.

Cell A2 was created by copying A1 and pasting its source value.. so there should not be any special character differences.

EVMS Payor Prep.xlsm
	A	B	C
1	z z z z1z0 z z z16z z zs 721 z z. z 3 zmy z00X z z17z 600 zsham z. zmy z00X z z17z 830 zempz z. zmy z00X z z17z 150 zingsley z zmy z00X z z17z 1309 zempz z. zmy z00X z z17z 301 zerview z. z 200 zmy z00X z z17z 301 zerview z. z 202 zmy z00X z z17z 850 zemppz z. zmy z00X z z17z 4201 zenwzzd z. zmy z00X z z17z 776 zaz Grzve z. zmy z00X z z17z 601 zlzen's z zmy z00X z z17z 890 zempz z. zmy z00X z z17z 1060 zirst Czlznial z. zmy z00X z z17z 828 z z 115 zmy z00X z z17z ziliated Przz Inzzrmatizn zzr 1003887118 721 z z. z 3 z 11/13z 600 zsham z. z 11/13z 1060 zirst Czlznial z. z 11/13z 4201 zenwzzd z. z 11/13z 601 zlzen's z z 11/13z 776 zaz Grzve z. zectuve z 11/13z 828 Healthly Way z 115 z 11/13z 150 zingsley z z 11/13z 830 zempz z. z 11/13z 1309 zempz z. z 11/13z 850 zempz z. z 11/13z 301 zerview z. z 200 z 9/13/2016 890 zempz z. z z1z0 301 zerview z. z 202 z z1z0
2	z z z z1z0 z z z16z z zs 721 z z. z 3 zmy z00X z z17z 600 zsham z. zmy z00X z z17z 830 zempz z. zmy z00X z z17z 150 zingsley z zmy z00X z z17z 1309 zempz z. zmy z00X z z17z 301 zerview z. z 200 zmy z00X z z17z 301 zerview z. z 202 zmy z00X z z17z 850 zemppz z. zmy z00X z z17z 4201 zenwzzd z. zmy z00X z z17z 776 zaz Grzve z. zmy z00X z z17z 601 zlzen's z zmy z00X z z17z 890 zempz z. zmy z00X z z17z 1060 zirst Czlznial z. zmy z00X z z17z 828 z z 115 zmy z00X z z17z ziliated Przz Inzzrmatizn zzr 1003887118 721 z z. z 3 z 11/13z 600 zsham z. z 11/13z 1060 zirst Czlznial z. z 11/13z 4201 zenwzzd z. z 11/13z 601 zlzen's z z 11/13z 776 zaz Grzve z. zectuve z 11/13z 828 Healthly Way z 115 z 11/13z 150 zingsley z z 11/13z 830 zempz z. z 11/13z 1309 zempz z. z 11/13z 850 zempz z. z 11/13z 301 zerview z. z 200 z 9/13/2016 890 zempz z. z z1z0 301 zerview z. z 202 z z1z0
3
4
5
6
7
8
9
10			TRUE
11			#VALUE!
vlookup issue

Cell Formulas
Range	Formula
C10	C10	=A1=A2
C11	C11	=VLOOKUP(A1,$A$2:$A$7,1,FALSE)

 

[Toadstool]

Hi ESACAWIP,

VLOOKUP and MATCH have a 255 character length limit for the lookup value. See this Microsoft page which explains a way of avoiding this challenge.
How to correct a #VALUE! error in the VLOOKUP function

If you'd prefer not to use an array formula then AGGREGATE will do it, see cell C12

Book2
	A	B	C
1	z z z z1z0 z z z16z z zs 721 z z. z 3 zmy z00X z z17z 600 zsham z. zmy z00X z z17z 830 zempz z. zmy z00X z z17z 150 zingsley z zmy z00X z z17z 1309 zempz z. zmy z00X z z17z 301 zerview z. z 200 zmy z00X z z17z 301 zerview z. z 202 zmy z00X z z17z 850 zemppz z. zmy z00X z z17z 4201 zenwzzd z. zmy z00X z z17z 776 zaz Grzve z. zmy z00X z z17z 601 zlzen's z zmy z00X z z17z 890 zempz z. zmy z00X z z17z 1060 zirst Czlznial z. zmy z00X z z17z 828 z z 115 zmy z00X z z17z ziliated Przz Inzzrmatizn zzr 1003887118 721 z z. z 3 z 11/13z 600 zsham z. z 11/13z 1060 zirst Czlznial z. z 11/13z 4201 zenwzzd z. z 11/13z 601 zlzen's z z 11/13z 776 zaz Grzve z. zectuve z 11/13z 828 Healthly Way z 115 z 11/13z 150 zingsley z z 11/13z 830 zempz z. z 11/13z 1309 zempz z. z 11/13z 850 zempz z. z 11/13z 301 zerview z. z 200 z 9/13/2016 890 zempz z. z z1z0 301 zerview z. z 202 z z1z0
2	z z z z1z0 z z z16z z zs 721 z z. z 3 zmy z00X z z17z 600 zsham z. zmy z00X z z17z 830 zempz z. zmy z00X z z17z 150 zingsley z zmy z00X z z17z 1309 zempz z. zmy z00X z z17z 301 zerview z. z 200 zmy z00X z z17z 301 zerview z. z 202 zmy z00X z z17z 850 zemppz z. zmy z00X z z17z 4201 zenwzzd z. zmy z00X z z17z 776 zaz Grzve z. zmy z00X z z17z 601 zlzen's z zmy z00X z z17z 890 zempz z. zmy z00X z z17z 1060 zirst Czlznial z. zmy z00X z z17z 828 z z 115 zmy z00X z z17z ziliated Przz Inzzrmatizn zzr 1003887118 721 z z. z 3 z 11/13z 600 zsham z. z 11/13z 1060 zirst Czlznial z. z 11/13z 4201 zenwzzd z. z 11/13z 601 zlzen's z z 11/13z 776 zaz Grzve z. zectuve z 11/13z 828 Healthly Way z 115 z 11/13z 150 zingsley z z 11/13z 830 zempz z. z 11/13z 1309 zempz z. z 11/13z 850 zempz z. z 11/13z 301 zerview z. z 200 z 9/13/2016 890 zempz z. z z1z0 301 zerview z. z 202 z z1z0
3
4
5
6
7
8
9
10			TRUE
11			#VALUE!
12			z z z z1z0 z z z16z z zs 721 z z. z 3 zmy z00X z z17z 600 zsham z. zmy z00X z z17z 830 zempz z. zmy z00X z z17z 150 zingsley z zmy z00X z z17z 1309 zempz z. zmy z00X z z17z 301 zerview z. z 200 zmy z00X z z17z 301 zerview z. z 202 zmy z00X z z17z 850 zemppz z. zmy z00X z z17z 4201 zenwzzd z. zmy z00X z z17z 776 zaz Grzve z. zmy z00X z z17z 601 zlzen's z zmy z00X z z17z 890 zempz z. zmy z00X z z17z 1060 zirst Czlznial z. zmy z00X z z17z 828 z z 115 zmy z00X z z17z ziliated Przz Inzzrmatizn zzr 1003887118 721 z z. z 3 z 11/13z 600 zsham z. z 11/13z 1060 zirst Czlznial z. z 11/13z 4201 zenwzzd z. z 11/13z 601 zlzen's z z 11/13z 776 zaz Grzve z. zectuve z 11/13z 828 Healthly Way z 115 z 11/13z 150 zingsley z z 11/13z 830 zempz z. z 11/13z 1309 zempz z. z 11/13z 850 zempz z. z 11/13z 301 zerview z. z 200 z 9/13/2016 890 zempz z. z z1z0 301 zerview z. z 202 z z1z0
Sheet1

Cell Formulas
Range	Formula
C10	C10	=A1=A2
C11	C11	=VLOOKUP(A1,$A$2:$A$7,1,FALSE)
C12	C12	=INDEX($A$2:$A$7,AGGREGATE(15,6,ROW($A$2:$A$7)-ROW($A$1)/($A$2:$A$7=A1),1))

 

[joeu2004]

Apparently, match functions (MATCH, VLOOKUP, COUNTIF, etc) limit the lookupValue (criterion) to 255 characters. But they still compare the entire strings in the lookupArray (!).

So, VLOOKUP(LEFT(A1,255), A1:A7, 1, FALSE) avoids the #VALUE error. But it still returns a #N/A error because 255 characters do not match 899 characters. (Klunk!)

You could replace your VLOOKUP formula with:

=IF(SUMPRODUCT(--(A1=$A$1:$A$7))>0, A1, NA())

And of course, you can replace NA() with anything else (e.g. the null string, "").

But I wonder if the VLOOKUP expression that you posted is truly what you are trying to do. It seems odd, as written.