How to use Vlookup function with strings that have more than 255 characters in Microsoft Excel?

Question

Hello everyone

In Excel, I have strings with more than 255 characters, and Vlookup function doesn't work with them, I use the following formula to search with Vlookup function, =VLOOKUP(D5,Sheet2!B1:C33,2,FALSE)

2 means column 2 which is the range that I want to obtain from it the final output.

The previous function doesn't work with string more than 255 characters.

so I'm seeking for other function without a specific limit.

In this website: https://stackoverflow.com/questions/13202473/way-to-overcome-excel-vlookup-function-limit-of-256-characters  I found the following function:

Function betterSearch(searchCell, A As Range, B As Range)
        For Each cell In A
            If cell.Value = searchCell.Value Then
                    betterSearch = B.Cells(cell.Row, 1)
                    Exit For
            End If
            betterSearch = "Not found"
        Next

End Function

I paste this function in VBA editor, the problem now is that actually I don't know how to use this function in the sheet, I tried to use the following functions and they don't work:

=betterSearch(D5, Sheet2!B1:C33, Sheet2!C1:C33)

=betterSearch(D5, Sheet2!C1:C33, Sheet2!B1:B33)

=betterSearch(D5, Sheet2!B1:B33, Sheet2!C1:C33)

none of these functions works with me.

Anyone knows how to use the previous function, or other function to search like Vlookup but without a specific limit of characters?

Answer 1

Try XLOOKUP() instead of VLOOKUP(). It's also faster

Answer 2

I send my file via private message. If it didn't work, you can also send your file in private message.

Answer 3

Hi Ahmed142,

Put the code in module 1.

=bettersearch(M1,Sheet1!A:A,Sheet1!B:B)

Sheet1!A:A And Sheet1!B:B is copy from J:J and K:K.

Best regards,

Snow Lu

Answer 4

Hi mister Pip Meadway1,

I tried XLOOKUP() function and the nice thing is that it can search for more than 255 characters, but I don't know if it has a specific limit for characters or it has no limit for the characters, but I'm sure after tying it, it can search for more than 255 characters.

Thank you.