Если в ваших данных встречаются символы «*» или «?», VLOOKUP может интерпретировать их как шаблоны (wildcards) и возвращать неверные совпадения. В статье показано, почему это происходит и какие простые способы используют для получения точных (exact) совпадений, когда значения содержат символы-джокеры.
Приведены рабочие формулы для современных версий Excel (XLOOKUP, MS365) и для старых версий (VLOOKUP, INDEX/MATCH), а также примеры с ожидаемыми результатами.
В чём проблема: wildcards внутри данных
VLOOKUP и некоторые другие функции Excel поддерживают символы-джокеры: «*» соответствует любому набору символов, «?» — одному символу. Это полезно при поиске по шаблону, но мешает, если сами данные содержат «*» или «?».
В примере у нас есть столбец Key с такими значениями, как «d*ef», «de*» и «d*f». При поиске с помощью VLOOKUP некоторые результаты окажутся неправильными из‑за интерпретации символов как wildcards.
Например, ожидаемые результаты: для поиска «de*» ожидается значение 6 (когда ключ равен «de*»), а для поиска «d*f» ожидается 9 (когда ключ равен «d*f»). VLOOKUP может вернуть другие значения или зависеть от порядка строк.
Решение 1: Используйте XLOOKUP (современный Excel)
Если у вас есть XLOOKUP (современные версии Excel), используйте её — она по умолчанию делает точные соответствия и не интерпретирует значения как wildcards, если не включать режим сопоставления по шаблону.
Пример формулы (вставьте в ячейку и при необходимости растяните/используйте динамическое заполнение):
=XLOOKUP(D2:D7,$A$2:$A$9,$B$2:$B$9)Примечание: в XLOOKUP параметр match_mode = 2 включает сопоставление с wildcard; по умолчанию этого не происходит, поэтому значения с «*» и «?» будут рассматриваться буквально.
Решение 2: Экранируйте символы-джокеры в VLOOKUP и XLOOKUP
Если вы хотите продолжать использовать VLOOKUP и при этом обеспечить поиск по точному тексту, экранируйте «*» (и при необходимости «?») заменой на «~*» (и «~?»). В Excel символ «~» используется как символ экранирования для wildcards.
Пример с VLOOKUP и обработкой ошибок (возвращает пустую строку вместо #N/A):
=IFNA(VLOOKUP(SUBSTITUTE(D2, "*", "~*"), $A$2:$B$9, 2, FALSE), "")Если у вас MS365, формулу можно «разлить» для диапазона:
=IFNA(VLOOKUP(SUBSTITUTE(D2:D7, "*", "~*"), A2:B9, 2, FALSE), "")Аналогичный подход с XLOOKUP (в примере используется режим 2 для демонстрации):
=XLOOKUP(SUBSTITUTE(D2:D7, "*", "~*"), A2:A9, B2:B9, "Oops Not Found!!", 2)Замечания по экранированию
В примерах заменяется только символ «*». Если в ваших данных встречаются также «?», замените его на «~?» с помощью SUBSTITUTE аналогичным образом. Если в данных есть сам символ «~», его предварительно может понадобиться экранировать (заменить на «~~»).
Решение 3: Для старых версий Excel — INDEX/MATCH
Для более старых версий Excel, где XLOOKUP недоступен, можно использовать комбинацию INDEX и MATCH для точного поиска. В примере приведена формула, которая ищет точное совпадение в массиве:
=INDEX($B$2:$B$9,MATCH(1,N($A$2:$A$9=C2),0))Эта формула использует массивное сравнение и позволяет получить точное соответствие даже при наличии символов «*» и «?» в данных.
Краткое резюме
Если значения содержат символы-джокеры и вы хотите точные совпадения:
- Используйте XLOOKUP в современных версиях Excel — по умолчанию она не включает wildcard-режим.
- Для VLOOKUP экранируйте «*» и «?» заменой на «~*» и «~?» через SUBSTITUTE, используйте IFNA для обработки ошибок.
- В старых версиях применяйте INDEX/MATCH (массивные формулы) для точного поиска.
