Загальна формула
=LOOKUP(2,1/SEARCH(things,A1),things)
Резюме
Щоб перевірити комірку на наявність однієї з кількох речей та повернути останню відповідність, знайдену у списку, ви можете використовувати формулу, засновану на функціях ПЕРЕГЛЯД і ПОШУК. У разі виявлення кількох збігів формула поверне останній збіг зі списку "речей".
У наведеному прикладі формула в C5 має вигляд:
=LOOKUP(2,1/SEARCH(things,B5),things)
Пояснення
Контекст: у вас є список речей у названому діапазоні "речі" (E5: E8), і ви хочете перевірити клітинки у стовпці B, щоб побачити, чи містять вони ці речі. Якщо так, ви хочете повернути останній предмет із “речей”, які були знайдені.
У цій формулі функція SEARCH використовується для пошуку комірок у стовпці B, як це:
SEARCH(things,B5)
Коли ПОШУК знаходить збіг, він повертає позицію збігу в комірці, яку шукають. Коли пошук не може знайти збіг, він повертає помилку #VALUE. Оскільки ми надаємо SEARH більше, ніж на пошуки, це поверне не один результат. У наведеному прикладі SEARCH повертає масив результатів, подібних до цього:
(8;24;#VALUE!;#VALUE!)
Потім цей масив використовується як дільник числа 1. Результатом є масив, що складається з помилок і десяткових значень. Помилки представляють речі, які не знайдено, а десяткові значення - речі, які знайшли. У наведеному прикладі масив виглядає так:
(0.125;0.0416666666666667;#VALUE!;#VALUE!)
Цей масив служить "вектором пошуку" для функції LOOKUP. Значення пошуку подається як число 2, а вектором результату є названий діапазон "речі". Це розумна частина.
Формула побудована таким чином, що вектор підстановки ніколи не буде містити значення більше 1, тоді як значення підстановки дорівнює 2. Це означає, що значення підстановки ніколи не буде знайдено. У цьому випадку LOOKUP буде відповідати останньому числовому значенню, знайденому в масиві, яке відповідає останній "речі", знайденої пошуком.
Нарешті, використовуючи названий діапазон "речі", наданий як вектор результату, LOOKUP повертає останню знайдену річ.
З жорстко закодованими значеннями
Використання такого діапазону, як "речі", дозволяє легко змінити список пошукових термінів (і додати більше пошукових термінів), але це не є вимогою. Ви також можете жорстко закодувати значення безпосередньо у формулу так:
=LOOKUP(2,1/SEARCH(("red","blue","green"),B5),("red","blue","green"))
