Way to overcome Excel Vlookup function limit of 256 characters

Question

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?

Answer 1

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

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

change ranges as required

Edit:

I mocked up a sample here - values in A2:A10 are the same as G2:G10 but in a different order. The length of each of those values is shown in column B, the VLOOKUP in column C fails on col A values > 255 chars but the INDEX/MATCH formula in col D works in all cases

https://www.dropbox.com/s/fe0sb6bkl3phqdr/vlookup.xls

Answer 2

I had the same problem and I've wrote this custom primitive vlookup. It doesn't care about the length of your cells' values.

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

PS Can't help but wonder why the original VLOOKUP written by professionals is implemented in this particular case more poorly than this 10-lined func?

Answer 3

This is a drag in replacement for Match() and is also optimised vba code unlike betterSearch above.

Public Function Match2(search As String, lookupArray As Range, Optional match_type As Integer = 0) As Long
  Application.Volatile
  Dim vArray As Variant
  vArray = lookupArray.Value
  For i = 1 To UBound(vArray, 1)
    If match_type = 0 Then
      If search = vArray(i, 1) Then
        Match2 = i
        Exit Function
      End If
    Else
      If match_type = -1 Then
        If search <= vArray(i, 1) Then
          Match2 = i
          Exit Function
        End If
      Else
        If search >= vArray(i, 1) Then
          Match2 = i
          Exit Function
        End If
      End If
    End If
  Next
End Function

Usage:

Index(rangeA, Match2(LookupValue, LookupRange, 0)

Above Ans said:

Can't help but wonder why the original VLOOKUP written by professionals is implemented in this particular case more poorly than this 10-lined func?

Optimisation and performance. If you limit the number of characters to 255 this requires only 2 operations on the CPU where as comparison of variable length strings takes many more steps on the CPU, because you have to repeatedly compare across 255 char widths. Programming languages like VBA obscure this a lot because all of the sub-operations are taken care for you.

For example, to compare 2 strings "Hello" and "abc" of fixed length 5 then we simply do the following operation on the CPU:

   0100100001100101011011000110110001101111 //Hello
-  0110000101100010011000110000000000000000 //abc
= -0000000000011000111111001111011010010100 //-419231380

Now you can simply ask whether the result is < 0, > 0, = 0 or even approximately 0. This can be done in 2 CPU operations. If cells are variable length (and formulae also), then first you'd have to use the CPU to pad out the end of the value with 0s to get the strings to the same length, before you can do the operations.

Answer 4

XLookup no longer has such limitation. I was able to Lookup > 500 Characters with it.