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