Общая формула
=XLOOKUP("*"&value&"*",lookup,results,,2)
Резюме
Чтобы использовать XLOOKUP для сопоставления значений, содержащих определенный текст, вы можете использовать подстановочные знаки и конкатенацию. В показанном примере формула в F5:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2)
где код (B5: B15) и количество (C5: C15) являются именованными диапазонами.
Объяснение
Функция XLOOKUP содержит встроенную поддержку подстановочных знаков, но эту функцию необходимо включить явно, установив режим сопоставления на число 2.
В показанном примере XLOOKUP настроен на соответствие значению, введенному в ячейку E5, которое может появиться в любом месте подстановочных значений в B5: B15. Формула в F5:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2) // returns 50
- lookup_value - E5, со звездочками (*), соединенными спереди и сзади
- lookup_array - код именованного диапазона (B5: B15)
- return_array - количество именованного диапазона (C5: C15)
- if_not_found - строка "нет совпадения"
- match_mode - предоставляется как 2 (совпадение с подстановочным знаком)
- search_mode - не предусмотрено. По умолчанию 1 (от первого до последнего)
Для автоматического соответствия типа «содержит» символ звездочки (*) добавляется к значению в ячейке E5 с конкатенацией:
"*"&E5&"*"
После объединения формула становится:
=XLOOKUP("*BCC*",code,quantity,"no match",2)
XLOOKUP находит первое совпадение, содержащее «BCC» (050-BCC-123 в строке 10), и возвращает соответствующее значение из возвращаемого массива, 50.
Обратите внимание, что XLOOKUP не чувствителен к регистру, ввод «скрытая копия» в E5 вернет тот же результат:
=XLOOKUP("*bcc*",code,quantity,"no match",2) // returns 50
См. Ниже вариант настройки XLOOKUP для соответствия с учетом регистра.
Опция ВПР
Формула ВПР также поддерживает подстановочные знаки, если задано точное соответствие. Эквивалентная формула ВПР для этого примера:
=VLOOKUP("*"&E5&"*",B5:C15,2,0)
Полное объяснение здесь.
С ПОИСКОМ и НАЙТИ
Также можно использовать функции ПОИСК и НАЙТИ, чтобы выполнить сопоставление типа "содержит" с помощью XLOOKUP. Для совпадения без учета регистра (как в примере выше) вы можете использовать ПОИСК следующим образом:
=XLOOKUP(1,--ISNUMBER(SEARCH("BCC",code)),quantity,"no match",2)
Для совпадения с учетом регистра вы можете использовать FIND:
=XLOOKUP(1,--ISNUMBER(FIND("BCC",code)),quantity,"no match",2)
Оба указанных выше варианта упрощают расширение критериев для включения других условий с использованием логической логики.
Здесь объясняется логика ISNUMBER + SEARCH.
Множественные совпадения
Если вам нужно несколько совпадений, см. Функцию ФИЛЬТР.
