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

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

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

Чтобы получить первое совпадающее ключевое слово, содержащееся в ячейках по сравнению со столбцом Ключевые слова, как показано выше, это означает, что нам нужно выполнить частичное совпадение, а не точное. Для этого вы можете объединить функции ISNUMBER и SEARCH вместе, чтобы получить массив ИСТИНА и ЛОЖЬ. Затем используйте MATCH для определения первого значения ИСТИНА и передайте номер позиции в INDEX для получения значения на этой позиции.

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

=INDEX(keyword_rng,MATCH(TRUE,ISNUMBER(SEARCH(keyword_rng,lookup_cell)),0))

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

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

=INDEX(($E$5:$E$7,MATCH(TRUE,ISNUMBER(SEARCH(($E$5:$E$7,B5)),0))

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

Объяснение формулы

=INDEX(($E$5:$E$7,MATCH(TRUE,ISNUMBER(SEARCH($E$5:$E$7,B5)),0))

• SEARCH($E$5:$E$7,B5): Функция SEARCH возвращает позицию ключевых слов из диапазона $E$5:$E$7, если они найдены в числовых значениях, и ошибку #VALUE!, если не найдены. Результат будет в виде массива, например: {15;11;#VALUE!}.
• ISNUMBER(ISNUMBER(SEARCH($E$5:$E$7,B5))) = ISNUMBER(ISNUMBER({15;11;#VALUE!})): Функция ISNUMBER преобразует числа в массиве в ИСТИНА и значения #VALUE! в ЛОЖЬ следующим образом: {ИСТИНА;ИСТИНА;ЛОЖЬ}.
• MATCH(TRUE,MATCH(TRUE,ISNUMBER(SEARCH($E$5:$E$7,B5)),0),0) = MATCH(TRUE,MATCH(TRUE,{ИСТИНА;ИСТИНА;ЛОЖЬ},0),0): Тип соответствия 0 заставляет функцию MATCH выполнять точное соответствие и возвращать позицию первого ИСТИНА в массиве {ИСТИНА;ИСТИНА;ЛОЖЬ}. Таким образом, функция вернет 1.
• INDEX(($E$5:$E$7,MATCH(TRUE,ISNUMBER(SEARCH($E$5:$E$7,B5)),0)) = INDEX(($E$5:$E$7,1): Функция INDEX затем возвращает первое значение в диапазоне $E$5:$E$7, которое является AAA.

Примечание

• Если ячейка содержит два или более ключевых слова, формула вернет первое ключевое слово, указанное в диапазоне ключевых слов.
• Формула не чувствительна к регистру. Чтобы выполнить поиск с учетом регистра, вы можете просто заменить функцию SEARCH на FIND.
• Вы также можете заменить список ключевых слов жестко заданной строкой в виде константы массива (например, {"AAA"}), если вы хотите только проверить, содержат ли ячейки эту строку.

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

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