Общая формула
=LOOKUP(2,1/SEARCH(things,A1),things)
Резюме
Чтобы проверить ячейку на наличие одного из нескольких параметров и вернуть последнее совпадение, найденное в списке, вы можете использовать формулу, основанную на функциях ПРОСМОТР и ПОИСК. В случае обнаружения нескольких совпадений формула вернет последнее совпадение из списка «вещей».
В показанном примере формула C5 имеет следующий вид:
=LOOKUP(2,1/SEARCH(things,B5),things)
Объяснение
Контекст: у вас есть список вещей в названном диапазоне «вещи» (E5: E8), и вы хотите проверить ячейки в столбце B, чтобы узнать, содержат ли они эти вещи. Если это так, вы хотите вернуть последний найденный элемент из «вещей».
В этой формуле функция ПОИСК используется для поиска ячеек в столбце B следующим образом:
SEARCH(things,B5)
Когда ПОИСК находит совпадение, он возвращает позицию совпадения в просматриваемой ячейке. Если поиск не может найти совпадение, он возвращает ошибку #VALUE. Поскольку мы даем SEARH более одного объекта для поиска, он вернет более одного результата. В показанном примере ПОИСК возвращает массив таких результатов:
(8;24;#VALUE!;#VALUE!)
Этот массив затем используется в качестве делителя числа 1. Результатом является массив, состоящий из ошибок и десятичных значений. Ошибки представляют собой вещи, которые не были найдены, а десятичные значения представляют найденные вещи. В показанном примере массив выглядит так:
(0.125;0.0416666666666667;#VALUE!;#VALUE!)
Этот массив служит «вектором поиска» для функции ПРОСМОТР. Значение поиска предоставляется как число 2, а вектор результатов - это именованный диапазон «вещи». Это умная часть.
Формула построена таким образом, что вектор поиска никогда не будет содержать значение больше 1, в то время как значение поиска равно 2. Это означает, что значение поиска никогда не будет найдено. В этом случае LOOKUP будет соответствовать последнему числовому значению, найденному в массиве, которое соответствует последней "вещи", найденной SEARCH.
Наконец, используя именованный диапазон "вещи", предоставленный в качестве вектора результатов, ПРОСМОТР возвращает последнее найденное.
С жестко запрограммированными значениями
Использование диапазона, такого как «вещи», упрощает изменение списка условий поиска (и добавление дополнительных условий поиска), но это не является обязательным требованием. Вы также можете жестко закодировать значения прямо в формуле следующим образом:
=LOOKUP(2,1/SEARCH(("red","blue","green"),B5),("red","blue","green"))
