Общая формула
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Резюме
Чтобы проверить ячейку на наличие одной из нескольких строк и вернуть настраиваемый результат для первого найденного совпадения, вы можете использовать формулу ИНДЕКС / ПОИСКПОЗ, основанную на функции ПОИСК. В показанном примере формула C5 имеет следующий вид:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
где объекты (E5: E8) и результаты (F5: F8) являются именованными диапазонами.
Это формула массива, и ее нужно вводить с помощью Control + Shift + Enter.
Объяснение
В этой формуле используются два именованных диапазона: вещи и результаты . Если вы переносите эту формулу напрямую, обязательно используйте именованные диапазоны с одинаковыми именами (определенные на основе ваших данных). Если вы не хотите использовать именованные диапазоны, используйте вместо них абсолютные ссылки.
В основе этой формулы лежит следующий фрагмент:
ISNUMBER(SEARCH(things,B5)
Это основано на другой формуле (подробно описанной здесь), которая проверяет ячейку на наличие единственной подстроки. Если ячейка содержит подстроку, формула возвращает ИСТИНА. Если нет, формула возвращает ЛОЖЬ.
Потому что мы даем функция поиска с более чем одну вещь , чтобы искать в именованный диапазон вещей , это даст нам больше одного результата, в массиве , который выглядит следующим образом :
(#VALUE!;9;#VALUE!;#VALUE!)
Числа обозначают совпадения в вещах , ошибки - элементы, которые не были найдены.
Чтобы упростить массив, мы используем функцию ISNUMBER для преобразования всех элементов в массиве в ИСТИНА или ЛОЖЬ. Любое действительное число становится ИСТИННЫМ, а любая ошибка (т. Е. Что-то не найдено) становится ЛОЖНОЙ. Результатом является такой массив:
(FALSE;TRUE;FALSE;FALSE)
который входит в функцию ПОИСКПОЗ как аргумент lookup_array со значением lookup_value, равным TRUE:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
Затем ПОИСКПОЗ возвращает позицию первого найденного ИСТИНА, в данном случае 2.
Наконец, мы используем функцию INDEX для получения результата от имени диапазона результатов в том же положении:
=INDEX(results,2) // returns "found red"
Вы можете настроить диапазон результатов любыми значениями, которые имеют смысл в вашем случае использования.
Предотвращение ложных совпадений
Одна проблема с этим подходом с подходом ISNUMBER + SEARCH заключается в том, что вы можете получить ложные совпадения из частичных совпадений внутри более длинных слов. Например, если вы попытаетесь сопоставить «dr», вы также можете найти «Andrea», «drank», «drip» и т. Д., Поскольку «dr» появляется внутри этих слов. Это происходит из-за того, что ПОИСК автоматически выполняет сопоставление типа "содержащий".
Для быстрого исправления вы можете заключить поисковые слова в символы пробела (например, «dr» или «dr»), чтобы не найти «dr» в другом слове. Но это не удастся, если "dr" появится первым или последним в ячейке.
Если вам нужно более надежное решение, один из вариантов - сначала нормализовать текст во вспомогательном столбце и добавить начальный и конечный пробелы. Затем используйте формулу на этой странице в тексте вспомогательного столбца вместо исходного текста.
