Загальна формула
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Резюме
Щоб протестувати комірку для однієї з кількох рядків та повернути власний результат для першого знайденого збігу, можна використовувати формулу INDEX / MATCH на основі функції SEARCH. У наведеному прикладі формула в С5 має вигляд:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
де речі (E5: E8) і результати (F5: F8) називаються діапазонами.
Це формула масиву, яку потрібно вводити за допомогою Control + Shift + Enter.
Пояснення
Ця формула використовує два іменовані діапазони: речі та результати . Якщо ви переносите цю формулу безпосередньо, обов’язково використовуйте іменовані діапазони з однаковими іменами (визначені на основі ваших даних). Якщо ви не хочете використовувати іменовані діапазони, використовуйте натомість абсолютні посилання.
Основою цієї формули є цей фрагмент:
ISNUMBER(SEARCH(things,B5)
Це базується на іншій формулі (докладно поясненій тут), яка перевіряє клітинку на наявність одного підрядка. Якщо комірка містить підрядок, формула повертає TRUE. Якщо ні, формула повертає FALSE.
Оскільки ми надаємо функції SEARCH більше одного, на що слід звернути увагу, у названих діапазонах речей це дасть нам більше єдиного результату в масиві, який виглядає так:
(#VALUE!;9;#VALUE!;#VALUE!)
Цифри представляють збіги в речах , помилки - елементи, які не були знайдені.
Для спрощення масиву ми використовуємо функцію ISNUMBER, щоб перетворити всі елементи масиву на TRUE або FALSE. Будь-яке дійсне число стає TRUE, а будь-яка помилка (тобто річ, яку не знайшли) стає FALSE. Результат - такий масив:
(FALSE;TRUE;FALSE;FALSE)
який переходить у функцію MATCH як аргумент lookup_array, з lookup_value TRUE:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
Потім MATCH повертає позицію першої знайденої TRUE, у цьому випадку 2.
Нарешті, ми використовуємо функцію INDEX для отримання результату з іменованих результатів діапазону в тій самій позиції:
=INDEX(results,2) // returns "found red"
Ви можете налаштувати діапазон результатів відповідно до будь-яких значень, які мають сенс у вашому випадку використання.
Запобігання помилковим збігам
Однією з проблем цього підходу з підходом ISNUMBER + SEARCH є те, що ви можете отримувати помилкові збіги з часткових збігів всередині довших слів. Наприклад, якщо ви намагаєтесь відповідати "dr", ви також можете знайти "Andrea", "випив", "капати" тощо, оскільки в цих словах з'являється "dr". Це відбувається тому, що ПОШУК автоматично виконує збіг типу "містить".
Для швидкого виправлення ви можете обернути пошукові слова пробілами (тобто "dr" або "dr"), щоб уникнути пошуку "dr" в іншому слові. Але це не вдасться, якщо "dr" з'явиться першим або останнім у комірці.
Якщо вам потрібне більш надійне рішення, одним із варіантів є нормалізація тексту спочатку у допоміжному стовпці та додавання провідної та кінцевої пробілів. Потім використовуйте формулу на цій сторінці тексту у допоміжному стовпці замість оригінального тексту.
