Загальна формула
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Резюме
Щоб перевірити комірку на наявність однієї з кількох речей і повернути перший збіг, знайдений у списку, ви можете використовувати формулу INDEX / MATCH, яка використовує SEARCH або FIND для пошуку збігу. У наведеному прикладі формула в С5 має вигляд:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
де "речі" - це названий діапазон E5: E9.
Примітка: це формула масиву, яку потрібно вводити за допомогою Control + Shift + Enter.
Пояснення
У цьому прикладі ми маємо список кольорів у названому діапазоні, який називається "речі" (E5: E9). Ми хочемо перевірити текст у стовпці B, чи не містить він жодного з цих кольорів. Якщо так, ми хочемо повернути назву першого знайденого кольору.
Працюючи зсередини, ця формула використовує функцію ISNUMBER і функцію SEARCH для пошуку тексту в B5 для кожного кольору, зазначеного в "речах", наприклад:
ISNUMBER(SEARCH(things,B5)
Цей вираз базується на формулі (докладно поясненій тут), яка перевіряє клітинку на наявність одного підрядка. Якщо комірка містить підрядок, вираз повертає TRUE. Якщо ні, вираз повертає FALSE.
Коли ми надаємо цьому ПОШУКУ список речей (замість одного), поверне нам масив результатів. Кожен знайдений колір генерує числове положення, а кольори, не знайдені, генерують помилку:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
Потім функція ISNUMBER перетворює результати у значення TRUE / FALSE. Будь-яке число стає TRUE, а будь-яка помилка (не знайдена) стає FALSE. Результат - такий масив:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Цей масив повертається функції MATCH як аргумент масиву. Значення пошуку дорівнює TRUE, а тип відповідності встановлюється рівним нулю, щоб змусити точне збіг. Коли є відповідний колір, MATCH повертає позицію першої знайденої TRUE. Це значення подається у функцію INDEX як номер рядка, а як масив надається названий діапазон "речей". Коли є хоча б одне збіг, INDEX повертає колір у цій позиції. Коли збігу не знайдено, ця формула повертає помилку # N / A.
З жорстко закодованими значеннями
Якщо ви не хочете встановлювати зовнішній іменований діапазон на зразок "речей" у цьому прикладі, ви можете жорстко закодувати значення у формулу як "константи масиву", наприклад:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Отримати перший матч у клітинку
Мова тут досить заплутана, але наведена вище формула поверне перший збіг, знайдений у списку речей, які потрібно шукати. Якщо замість цього ви хочете повернути перший збіг, знайдений у клітинці, що перевіряється, ви можете спробувати формулу, подібну до цієї:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
У цій версії формули функція MATCH налаштована для пошуку результату цього фрагмента:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
який використовує функцію AGGREGATE, щоб отримати мінімальне значення в результатах, повернутих SEARCH. Тут нам потрібен AGGREGATE, оскільки вхідний масив, швидше за все, буде містити помилки (повертаються пошуком, коли речі не знайдені), і нам потрібна функція, яка буде ігнорувати ці помилки і все одно даватиме нам мінімальне числове значення.
Результат від AGGREGATE повертається безпосередньо до MATCH як значення пошуку, разом із тим самим масивом, який повертає SEARCH. Кінцевий результат - це перший збіг, знайдений у комірці, а не перший збіг, знайдений у списку речей.
