Excel: поиск списка строк в определенной строке с использованием формул массива?

Я хочу найти в ячейке список слов. Я думал, что это будет работать как формула массива:

{=FIND(<list of words I want to search for>,<cell I want to search>)}

Но он находит только совпадение, когда слово, которое в ячейке, которую я ищу, находится в первой строке списка слов, которые я ищу. Есть ли способ написать формулу, которая просматривает весь список? И я бы предпочел, чтобы он не просто вернул TRUE/FALSE. Я знаю, как искать ячейку для списка слов и возвращать TRUE/FALSE в зависимости от того, существует ли в ячейке слово в ячейке. Я хочу знать, какое слово было найдено, или его положение.

Ответ 1

Это вернет соответствующее слово или ошибку, если совпадение не найдено. В этом примере я использовал следующее.

Список слов для поиска: G1:G7
Ячейка для поиска в: A1

=INDEX(G1:G7,MAX(IF(ISERROR(FIND(G1:G7,A1)),-1,1)*(ROW(G1:G7)-ROW(G1)+1)))

В качестве формулы массива введите Ctrl + Shift + Enter.

Эта формула работает, сначала просматривая список слов, чтобы найти совпадения, а затем записывать положение слова в списке как положительное значение, если оно найдено или как отрицательное значение, если оно не найдено. Наибольшее значение из этого массива - позиция найденного слова в списке. Если слово не найдено, отрицательное значение передается в функцию INDEX(), вызывая ошибку.

Чтобы вернуть номер строки совпадающего слова, вы можете использовать следующее:

=MAX(IF(ISERROR(FIND(G1:G7,A1)),-1,1)*ROW(G1:G7))

Это также необходимо ввести как формулу массива, нажав Ctrl + Shift + Enter. Он вернет -1, если совпадение не найдено.

Ответ 2

Добавление этого ответа для таких людей, как я, для которых приемлемый ответ TRUE/FALSE

OR(IF(ISNUMBER(SEARCH($G$1:$G$7,A1)),TRUE,FALSE))

или с учетом регистра

OR(IF(ISNUMBER(FIND($G$1:$G$7,A1)),TRUE,FALSE))

Если диапазон поисковых терминов равен G1:G7

Запомните CTRL + SHIT + ВВОД

Ответ 3

Измените свой список слов с разделителем, который никогда не встречается в словах, т.е. |
переменные подкачки в find call - мы хотим, чтобы поиск, если значение ячейки соответствует одному из слов в строке шаблона {=FIND("cell I want to search","list of words I want to search for")}
Если шаблоны схожи, существует риск получить больше результатов, чем хотелось бы, мы ограничиваем только правильные результаты, добавляя & "|" к проверенному значению ячейки (хорошо работает с формулами массива) ячейка G3 может содержать: {=SUM(FIND($A$1:$A$100&"|";A3))} это гарантирует, что таблица будет сравнивать строки, такие как "cellvlaue |" againts "pattern1 |", "pattern2 |" и т.д., который сортирует конфликты, такие как pattern1 = "недавно добавленный", pattern2 = "добавлен" (сумма всех ячеек, соответствующих "добавленным", будет слишком высокой, включая целевые значения для ячеек, соответствующих "недавно добавленным", что было бы логичным ошибка)