Общая формула
=MATCH(1,EXACT(LEFT(A1,255),LEFT(rng,255))*EXACT(MID(A1,256,255),MID(rng,256,255)),0)
Резюме
Чтобы сопоставить текст длиной более 255 символов с помощью функции ПОИСКПОЗ, вы можете использовать функции LEFT, MID и EXACT для анализа и сравнения текста, как описано ниже. В показанном примере формула в G5:
=MATCH(1,EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255)),0)
где data - именованный диапазон B5: B15.
Примечание: эта формула выполняет сравнение с учетом регистра.
Объяснение
Функция ПОИСКПОЗ имеет ограничение в 255 символов для значения поиска. Если вы попытаетесь использовать более длинный текст, ПОИСКПОЗ вернет ошибку #VALUE.
Чтобы обойти это ограничение, вы можете использовать логическую логику и функции LEFT, MID и EXACT для анализа и сравнения текста.
Строка, которую мы тестируем в ячейке E5, состоит из 373 символов:
Lorem ipsum dolor amet поместила птицу в трастовый фонд списка, права на велосипеды с единорогами, вы, вероятно, не слышали о них, усы. Корм helvetica crusty семиотика фактически семейная реликвия. Tumblr poutine unicorn godard очень постарались, прежде чем они продали нарвал медитационный китч жилет fixie twee буквально с капюшоном в стиле ретро. Сумка-почтальон адского хрустящего зеленого сока artisan.
По сути, это просто формула MATCH, настроенная для поиска 1 в режиме точного совпадения:
=MATCH(1,array,0)
Массив в приведенной выше формуле содержит только единицы и нули, а единицы представляют совпадающий текст. Этот массив состоит из следующего выражения:
EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255))
Само это выражение состоит из двух частей. Слева у нас есть:
EXACT(LEFT(E5,255),LEFT(data,255)) // compare first 255 chars
Здесь функция LEFT извлекает первые 255 символов из E5 и из всех ячеек в данных именованного диапазона (B5: B15). Поскольку данные содержат 11 текстовых строк, LEFT выдаст 11 результатов.
Затем функция EXACT сравнивает одну строку из E5 со всеми 11 строками, возвращаемыми LEFT. EXACT возвращает 11 результатов в виде такого массива:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
Справа у нас есть еще одно выражение:
EXACT(MID(E5,256,255),MID(data,256,255) // compare next 255 chars
Это точно такой же подход, как и для LEFT, но здесь мы используем функцию MID для извлечения следующих 255 символов текста. Функция EXACT снова возвращает 11 результатов:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
Когда два вышеуказанных массива умножаются друг на друга, математическая операция переводит значения ИСТИНА ЛОЖЬ в единицы и нули. Следуя правилам логической арифметики, результатом будет такой массив:
(0;0;0;0;0;0;0;0;0;1;0)
который возвращается непосредственно в MATCH как поисковый массив. Теперь формулу можно разрешить так:
=MATCH(1,(0;0;0;0;0;0;0;0;0;1;0),0)
Функция ПОИСКПОЗ выполняет точное совпадение и возвращает конечный результат 10, который представляет десятую текстовую строку в B5: B15.
Примечание: длина текста, показанная в примере, рассчитывается с помощью функции ДЛИН. Он отображается только для справки.
Вариант без учета регистра
Функция EXACT чувствительна к регистру, поэтому в приведенной выше формуле учитывается регистр.
Чтобы выполнить сопоставление с длинным текстом без учета регистра, вы используете функции ISNUMBER и SEARCH следующим образом:
=MATCH(1,ISNUMBER(SEARCH(LEFT(E5,255),LEFT(data,255)))*ISNUMBER(SEARCH(MID(E5,256,255),MID(data,256,255))),0)
Общая структура этой формулы идентична приведенному выше примеру, но функция ПОИСК используется вместо ТОЧНО для сравнения текста (подробно объясняется здесь).
В отличие от EXACT, функция SEARCH также поддерживает подстановочные знаки.
С XMATCH
Функция XMATCH не имеет того же ограничения в 255 символов, что и MATCH. Чтобы выполнить аналогичное сопоставление с длинным текстом с помощью XMATCH, вы можете использовать гораздо более простую формулу ниже:
=XMATCH(E5,data)
Примечание. XMATCH поддерживает подстановочные знаки, но не чувствителен к регистру.
