Загальна формула
=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
Формула VLOOKUP також підтримує символи підстановки, якщо встановлено точну відповідність. Еквівалентна формула VLOOKUP для цього прикладу:
=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.
Кілька збігів
Якщо вам потрібно кілька збігів, див. Функцію ФІЛЬТР.
