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.


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.