Получить первое совпадающее значение в ячейке по списку

Предположим, у вас есть список ключевых слов, и вам нужно получить первое ключевое слово, которое появилось в определенной ячейке, при этом ячейка содержит несколько других значений. Для этого вам придется использовать формулу INDEX и MATCH с помощью функций AGGREGATE и SEARCH.

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

Чтобы получить первое совпадающее ключевое слово в ячейке по списку Ключевых слов, как показано в таблице выше, это означает, что нам нужно выполнить частичное совпадение, а не точное. Для этого вы можете использовать функцию SEARCH, чтобы передать позиции ключевых слов, найденных в ячейке, в числовом формате, в функцию AGGREGATE. Затем AGGREGATE получит наименьшее число, установив function_num равным 15, а аргумент ref2 равным 1. После этого используйте MATCH для определения первого наименьшего значения и передайте номер позиции в INDEX для получения значения в этой позиции.

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

=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, чтобы получить результат:

=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($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)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0))

• SEARCH($E$5:$E$7,B5): Функция SEARCH возвращает позицию каждого ключевого слова из диапазона $E$5:$E$7, когда оно найдено в числовом формате, и ошибку #VALUE!, если не найдено. Результат будет в виде массива, например: {15;11;#VALUE!}.
• AGGREGATE(15,6,AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),1) = AGGREGATE(15,6,AGGREGATE(15,6,{15;11;#VALUE!},1),1): Функция AGGREGATE с function_num равным 15 и option равным 6 вернет наименьшее значение в массиве согласно аргументу ref2 равному 1, игнорируя ошибочные значения. Таким образом, фрагмент вернет 11.
• MATCH(MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),1),,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0),0) = MATCH(MATCH(11,,{15;11;#VALUE!},0),0): Тип соответствия 0 заставляет функцию MATCH выполнять точное соответствие и возвращать позицию 11 в массиве {15;11;#VALUE!}. Таким образом, функция вернет 2.
• INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0)) = INDEX($E$5:$E$7,2): Функция INDEX затем возвращает второе значение в диапазоне $E$5:$E$7, которое является bbb.

Примечание

• Если в ячейке нет ключевых слов, будет возвращена ошибка #NUM!.
• Формула не чувствительна к регистру. Чтобы выполнить поиск с учетом регистра, вы можете просто заменить функцию SEARCH на FIND.

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

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