Way to overcome Excel Vlookup function limit of 256 characters

Question

I have an excel array that has numerous values. Some have more than 256 characters, while others have fewer than 256.

I was able to receive results when I used a sample string to perform a VLookup and it matched the rows with fewer than 256 characters. Rows with more than 256 characters return a "#N/A."

Is there a way for me to get around this limit using Vlookup or another built-in Excel function?

Answer 1

If you are using VLOOKUP like this:

=VLOOKUP(A2,D2:Z10,3,FALSE)

i.e. looking up A2 in D2:D10 and returning a result from F2:F10 then try this formula instead:

=INDEX(F2:F10,MATCH(TRUE,INDEX(D2:D10=A2,0),0))

Change ranges as required.