Формула множественного поиска в Excel

Question

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

Я хотел бы, чтобы результатом формулы были слова, которые я выбрал.

Пример этого у меня есть, если одна ячейка показывает список ингредиентов, а другая ячейка показывает список аллергенов. Мне нужна формула для поиска в списке ингредиентов и выбора аллергенов.

Я знаю, что мог бы использовать функцию поиска, чтобы она возвращалась с одним результатом, а затем объединяла результаты, но должен быть более простой способ!

Спасибо

Answer 1

В ячейке C6 вы можете использовать приведенную ниже формулу,

=MID(C3,FIND("Gluten",C3),LEN("Gluten"))&", "&MID(C3,FIND("Peanuts",C3),LEN("Peanuts"))

Вы можете добавить больше аллергенов для добавления, добавив ниже в конце формулы:

&", "&MID(C3,FIND("Another Allergen",C3),LEN("Another Allergen"))

И повторите по мере необходимости.

Вы также можете доказать это, если укажете аллерген, которого нет в ингредиентах:

=IFERROR(MID(C3,FIND("Gluten",C3),LEN("Gluten")),"")&", "&IFERROR(MID(C3,FIND("Peanuts",C3),LEN("Peanuts")),"")&", "&IFERROR(MID(C3,FIND("Another Allergen",C3),LEN("Another Allergen")),"")

Поскольку при этом используется ПОИСК, шаблон поиска чувствителен к регистру, возможно, вы захотите поменять его местами для функции ПОИСК, которая по-прежнему принимает те же 2 аргумента в том же порядке.

Answer 2

Если у вас есть Office 365 Excel, который представил TEXTJOIN, вы можете использовать его в формуле массива:

=TEXTJOIN(", ",,IF(ISNUMBER(FIND( {"GLUTEN","PEANUTS"},C3)),{"GLUTEN","PEANUTS"},""))

Будучи формулой Array, она должна быть подтверждена Ctrl-Shift-Enter вместо Enter при выходе из режима редактирования.

Это будет повторять два имени и проверять строку с помощью Find. Если найден, то Найти вернуть номер, если не ошибка. Мы находим, где он возвращает число, а затем второй массив вернет правильное значение в TEXT JOIN

---

Но лучшей практикой будет создание таблицы с перечисленными аллергенами:

Затем вы можете ссылаться на этот список в формуле TEXTJOIN вместо того, чтобы поддерживать его в самой формуле:

=TEXTJOIN(", ",,IF(ISNUMBER(SEARCH( ALLERGEN[ALLERGENS],C3)),ALLERGEN[ALLERGENS],""))

Все еще формула массива, и я переключился на ПОИСК, чтобы справиться с разницей в капитализации.

Теперь, когда нужны новые аллергены, они могут быть добавлены в список без необходимости обновления формулы.