3
2
Using excel 2003, the formula:
=VLOOKUP(D1 ,A1:B135, 2)
fails if the length of D1 exceeds 255 characters (i.e. the list has some text longer then 255 characters, D1 has the same text value, and VLOOKUP returns #VALUE!).
MATCH seems to suffer from the same character limit.
I cannot find any official confirmation of these limits, for example here:
http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx
or here:
http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx?CTT=3
I know that excel has a 255 limit on the length of text used in formulae, but it suggests connate should work (it does not in this case, and I am not using strings in the formula, but referencing another cell).
Can somebody confirm that these limit exist (it is always possible I am doing something else wrong)?
More importantly, does anyone know of a way around them?
Thanks
1Confirmed this behavior in both Excel 2003 and 2007. – Sux2Lose – 2011-02-18T14:11:06.427
Same issue (in Excel 2003) – wilson – 2011-02-19T20:46:35.317
Here is the solution which worked perfectly for me: http://stackoverflow.com/questions/13202473/way-to-overcome-excel-vlookup-function-limit-of-256-characters
– None – 2013-09-11T14:45:04.317Check below link - worked perfectly for me:
http://stackoverflow.com/questions/13202473/way-to-overcome-excel-vlookup-function-limit-of-256-characters