excelvlookup

Way to overcome Excel Vlookup function limit of 256 characters

I have a excel array with multiple values. Some are less than 256 characters and some have a length greater than 256.

When I tried to do a VLookup using a sample string, I can get results when it matches the rows with less than 256 characters. For rows greater that 256 characters, it returns a '#N/A'.

Is there a way of using Vlookup or by using some other in-built function in Excel that I can overcome this limit?

Solution

  • 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. XLOOKUP doesn't have the same character restriction as VLOOKUP

    =XLOOKUP(A2,D2:D10,F2:F10)

    That works in Excel 2021 or later versions. For older versions you can use

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

    change ranges as required