VLOOKUP 256 Character Limit Solution

[Chlwls808]

Hi,

I have a VLOOKUP function which searches values in one column for a keyword located in a separate cell. The problem is that because there's a 256 character limit for any VLOOKUP, any string that goes beyond that will result in #N/A.

I've seen some VBA solutions out there that can fix this type of issues but it didn't work for my scenario (at least I wasn't able to get it to work).

Currently, my VLOOKUP function looks like this:

=VLOOKUP(G15&"*",D3:D32,1,0)

I've tried using combination of Index and match function like below, but it doesn't work:

=INDEX(D3:D32,MATCH(TRUE,INDEX(D3:D32=G15&"*",0),0))

Does anyone know how to resolve this issue?

Appreciate your help!

 

[RoryA]

Try something like:

=lookup(2,1/ISNUMBER(SEARCH(G15,D3:D32)),D3:D32)

 

[Chlwls808]

Try something like:

=lookup(2,1/ISNUMBER(SEARCH(G15,D3:D32)),D3:D32)

Thank you! You saved the day!