Общая формула
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Резюме
Чтобы проверить ячейку на одно из нескольких значений и вернуть первое совпадение, найденное в списке, можно использовать формулу ИНДЕКС / ПОИСКПОЗ, которая использует ПОИСК или НАЙТИ для поиска совпадения. В показанном примере формула C5 имеет следующий вид:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
где «вещи» - именованный диапазон E5: E9.
Примечание. Это формула массива, которую необходимо вводить с помощью Control + Shift + Enter.
Объяснение
В этом примере у нас есть список цветов в именованном диапазоне, который называется «вещи» (E5: E9). Мы хотим проверить текст в столбце B, чтобы узнать, содержит ли он какой-либо из этих цветов. Если это так, мы хотим вернуть имя первого найденного цвета.
Работая изнутри, эта формула использует функцию ЕЧИСЛО и функцию ПОИСК для поиска текста в B5 для каждого цвета, указанного в «вещах», например:
ISNUMBER(SEARCH(things,B5)
Это выражение основано на формуле (подробно описанной здесь), которая проверяет ячейку на наличие единственной подстроки. Если ячейка содержит подстроку, выражение возвращает ИСТИНА. В противном случае выражение возвращает FALSE.
Когда мы дадим этому ПОИСКУ список вещей (вместо одного), мы вернем нам массив результатов. Каждый найденный цвет будет генерировать числовую позицию, а не найденные цвета будут генерировать ошибку:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
Затем функция ЕЧИСЛО преобразует результаты в значения ИСТИНА / ЛОЖЬ. Любое число становится ИСТИННЫМ, а любая ошибка (не найденная) становится ЛОЖНОЙ. Результатом является такой массив:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Этот массив возвращается в функцию MATCH в качестве аргумента массива. Значение поиска - ИСТИНА, а тип соответствия установлен на ноль, чтобы обеспечить точное соответствие. Когда есть соответствующий цвет, ПОИСКПОЗ возвращает первую найденную позицию ИСТИНА. Это значение передается в функцию ИНДЕКС как номер строки, а именованный диапазон «вещи» предоставляется в виде массива. Когда есть хотя бы одно совпадение, ИНДЕКС возвращает цвет в этой позиции. Если совпадений не найдено, эта формула возвращает ошибку # Н / Д.
С жестко запрограммированными значениями
Если вы не хотите настраивать внешний именованный диапазон, такой как «вещи» в этом примере, вы можете жестко закодировать значения в формуле как «константы массива» следующим образом:
(=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))
В этой версии формулы функция ПОИСКПОЗ настроена для поиска результата этого фрагмента:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
который использует функцию АГРЕГАТ для получения минимального значения в результатах, возвращаемых функцией ПОИСК. Здесь нам нужен AGGREGATE, потому что входящий массив, вероятно, будет содержать ошибки (возвращаемые функцией SEARCH, когда что-то не найдено), и нам нужна функция, которая будет игнорировать эти ошибки и по-прежнему давать минимальное числовое значение.
Результат AGGREGATE возвращается непосредственно MATCH в качестве значения поиска вместе с тем же массивом, возвращаемым SEARCH. Конечный результат - это первое совпадение, найденное в ячейке, а не первое совпадение, найденное в списке вещей.
