Не более 255 символов на VLOOKUP

Question

Используя Excel 2003, формула:

=VLOOKUP(D1 ,A1:B135, 2)

терпит неудачу, если длина D1 превышает 255 символов (то есть список имеет некоторый текст длиннее 255 символов, D1 имеет то же текстовое значение, и VLOOKUP возвращает #VALUE!).

МАТЧ, похоже, страдает от того же предела персонажа.

Я не могу найти никакого официального подтверждения этих ограничений, например здесь:

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

или здесь:

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

Я знаю, что в Excel есть ограничение в 255 по длине текста, используемого в формулах, но он предполагает, что connate должен работать (в данном случае это не так, и я не использую строки в формуле, а ссылаюсь на другую ячейку).

Может ли кто-нибудь подтвердить, что эти ограничения существуют (всегда возможно, что я делаю что-то еще неправильно)?

Что еще более важно, кто-нибудь знает способ обойти их?

Спасибо

Answer 1

Вы всегда можете написать свой собственный VLOOKUP ... но, в качестве альтернативы, вы можете вычислить хеш из значения поиска и использовать его в качестве нового значения поиска.

Могут происходить коллизии, но при использовании довольно приличного хэш-алгоритма, такого как MD5, это не должно быть проблемой.

Чтобы создать хеш-функцию, вы можете использовать идеи из https://stackoverflow.com/questions/125785/password-hash-function-for-excel-vba.

Answer 2

Напишите свой собственный поиск в VBA

Вот начало:

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

Answer 3

Я искал точные совпадения между двумя отчетами и столкнулся с этим. Я объединил несколько различных полей одного отчета и другого отчета в одну длинную строку, а затем использовал Vlookup, чтобы определить, совпадает ли каждое поле из 1000 записей или около того между двумя отчетами. Вот когда я столкнулся с проблемой.

Поскольку это было единоразово, и я мог разбить его на более мелкие куски, сгруппировав менее 255 символов в 3 отдельных столбца, я сделал это и написал 3 отдельных поиска и сравнил данные в 3 кусках, а не все сразу. Основным недостатком является необходимость повторной сортировки по таблице поиска (столбцу), в которой я хотел запустить поиск для каждой из 3 подгрупп перед запуском каждого поиска.

Я бы назвал мое решение вполне приемлемым для одноразовой вещи, но если бы мне пришлось делать это более одного раза, я бы искал что-то более эффективное.