Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Using excel 2003, the formula:

=VLOOKUP(D1 ,A1:B135, 2)

fails if the length of D1 exceeds 255 characters (i.e. the list has some text longer then 255 characters, D1 has the same text value, and VLOOKUP returns #VALUE!).

MATCH seems to suffer from the same character limit.

I cannot find any official confirmation of these limits, for example here:

http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx

or here:

http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx?CTT=3

I know that excel has a 255 limit on the length of text used in formulae, but it suggests connate should work (it does not in this case, and I am not using strings in the formula, but referencing another cell).

Can somebody confirm that these limit exist (it is always possible I am doing something else wrong)?

More importantly, does anyone know of a way around them?

Thanks

share|improve this question
1  
Confirmed this behavior in both Excel 2003 and 2007. –  Sux2Lose Feb 18 '11 at 14:11
    
Same issue (in Excel 2003) –  wilson Feb 19 '11 at 20:46
    
Here is the solution which worked perfectly for me: stackoverflow.com/questions/13202473/… –  user253260 Sep 11 '13 at 14:45
    
Check below link - worked perfectly for me: stackoverflow.com/questions/13202473/… –  user253260 Sep 11 '13 at 14:45

3 Answers 3

Write your own lookup in VBA

Here's a start:

Function MyVL(v As Range, r As Range, os As Long) As Variant
    Dim cl As Range

    For Each cl In r.Columns(1).Cells
        If v = cl Then
            MyVL = cl.Offset(0, os - 1)
            Exit Function
        End If
    Next
End Function
share|improve this answer

You can always code your own VLOOKUP... but, as an alternative, you can compute a hash from your lookup value, and use it as a new lookup value.

Collisions may happen, but using a somewhat decent hash algorithm, like MD5, it shouldn't be a problem.

To create a hash function, you may use the ideas from http://stackoverflow.com/questions/125785/password-hash-function-for-excel-vba.

share|improve this answer
    
There's also superuser.com/questions/550592/…, where the OP is wanting to hash specifically as they may want to use VLOOKUP later. –  ernie May 31 '13 at 16:56

I was searching for exact matches between 2 reports and ran into this. I concatenated the several different fields of one report and another report into one LONG string and then used Vlookup to identify if every field of 1000 records or so matched between the 2 reports. That's when I ran into the problem.

Because it was a one-time thing and I could break it down into smaller chunks by concatenating less than 255 characters into 3 separate columns, I did that and wrote 3 separate lookups and compared the data in 3 chunks rather than all at once. The major drawback is that I had to re-sort by the lookup table (column) I wanted to run the lookup on for each of the 3 sub groups before running each lookup.

Id call my solution perfectly acceptable for a onetime thing, but if I had to do it more than once, I'd look for something more efficient.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.