Извлечение первого совпадающего значения из ячейки по списку

Предположим, у вас есть список ключевых слов. Чтобы извлечь первое ключевое слово, встречающееся в определённой ячейке (при условии, что ячейка содержит несколько других значений), используйте формулу с функциями ИНДЕКС и ПОИСКПОЗ, дополнительно задействовав функции AGGREGATE и ПОИСК.

Как извлечь первое совпадающее значение из ячейки на основе списка?

Чтобы получить первое совпадающее ключевое слово в ячейке по сравнению со списком ключевых слов, как показано в таблице выше, нужно выполнить частичное совпадение, а не точное. Для этого используйте функцию ПОИСК, чтобы передать позиции найденных в ячейке ключевых слов (в виде чисел) в функцию AGGREGATE. Затем функция AGGREGATE определит наименьшее число, если установить параметр function_num в значение 15, а аргумент ref2 — в значение 1. После этого с помощью функции ПОИСКПОЗ находится позиция этого наименьшего значения, и полученный номер передаётся функции ИНДЕКС для извлечения соответствующего ключевого слова.

Общий синтаксис

=INDEX(keyword_rng,MATCH(AGGREGATE(15,6,SEARCH(keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))

√ Примечание. Это формула массива, которую необходимо вводить с нажатием Ctrl+Shift+Enter.

Чтобы извлечь первое совпадающее ключевое слово, появившееся в ячейке B5, по сравнению со столбцом «Ключевые слова», скопируйте или введите приведённую ниже формулу в ячейку C5 и нажмите Ctrl+Shift+Enter, чтобы получить результат:

=ИНДЕКС()$E$5:$E$7,ПОИСКПОЗ(AGGREGATE(15,6,ПОИСК()))$E$5:$E$7,B5),1),ПОИСК($E$5:$E$7,B5),0))

√ Примечание. Знаки доллара ($) выше указывают на абсолютные ссылки, что означает, что диапазон keyword_rngв формуле не изменится при перемещении или копировании формулы в другие ячейки. Однако знаки доллара не добавлены к ячейке lookup_cell, поскольку требуется, чтобы она была динамической. После ввода формулы перетащите маркер заполнения вниз, чтобы применить формулу к ячейкам ниже.

Пояснение формулы

=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))

• SEARCH($E$5:$E$7,B5):Функция ПОИСК возвращает позицию каждого ключевого слова из диапазона $E$5:$E$7, если оно найдено (в виде числа), и ошибку #ЗНАЧ!, если не найдено. Результат представлен в виде массива следующего вида: {15;11;#ЗНАЧ!}.
• AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1)=AGGREGATE(15,6,){15;11;#ЗНАЧ!},1):Функция AGGREGATE с параметром function_num, равным 15, и параметром option, равным 6, возвращает наименьшее значение в массиве согласно аргументу ref2, равному 1, игнорируя при этом ошибки. Таким образом, данный фрагмент вернёт значение 11.
• ПОИСКПОЗ()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)=ПОИСКПОЗ()11,{15;11;#ЗНАЧ!},0): Аргумент match_type = 0 заставляет функцию ПОИСКПОЗ выполнить точное совпадение и вернуть позицию значения 11 в массиве {15;11;#ЗНАЧ!}. Таким образом, функция возвращает значение 2.
• ИНДЕКС($E$5:$E$7,)ПОИСКПОЗ()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)) = ИНДЕКС($E$5:$E$7,2):Затем функция ИНДЕКС возвращает 2-е значение из диапазона $E$5:$E$7, то есть bbb.

Примечание

• Если в ячейке отсутствуют ключевые слова, появится ошибка #ЧИСЛО!.
• Формула не учитывает регистр. Чтобы выполнить поиск с учётом регистра, просто замените функцию ПОИСК на функцию НАЙТИ.

Связанные функции

Связанные формулы