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 mister Snow Lu MSFT,

I tried to do like what you said but that didn't work with me:

I modified this reply and I deleted the link

Thanks.

Answer 5

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

Hi, mister Snow Lu MSFT,

Thank you so much, I tried your file and the function works very well, and thanks for telling me about that thing about the way of privet messagee in the community.