Getting Around the VLOOKUP 255 character limit

In Excel, it's not possible to use VLOOKUP if the value you are searching for in a range is longer than 255 characters. In this example, the value in cell B3 is longer than 255 characters:

. . . so it can't be found in the range G:H and we get a #VALUE! error, even though there is a match for this value in G7. (The lengths of each cell in column G are shown in column I.)

We can get around this problem by using the below function posted here,

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

This function will autofill when you type it in the cell.

=BetterSearch(A2,G:G,H:H)

Enter the searched for value, the column to find it in, and then the column the adjacent value should be returned from.

A search for value of greater than 255 characters gets a result.