Вы можете сделать это с помощью формулы массива. Для фиксированного диапазона формула в C2
будет иметь вид
=IF(MAX(IFERROR(SEARCH($A$2:$A$5,$B2),0))>0,"yes","no")
Обязательно введите это как формулу массива, используя Ctrl+Shift+Enter. Вы поймете, что это сработало, когда вы получите фигурные скобки {} на обоих концах формулы.
Если вы хотите, чтобы диапазон был динамическим, один хороший способ - определить именованный диапазон. Если вы попытаетесь использовать все столбцы A:A
то все вернет yes
потому что функции SEARCH
и FIND
возвращают TRUE
если искомая строка пуста. Поскольку A:A
будет содержать пробелы в вашем списке, все ваши результаты будут yes
и это не поможет. Вместо этого нам нужно определить динамический именованный диапазон. Есть несколько способов сделать это, но мне нравится открывать "Диспетчер имен" (он находится в середине ленты "Формулы" в разделе "Определенные имена"). Откройте его и нажмите "Новый" в левом верхнем углу. Дайте ему имя, например « Keywords
и формулу "Относится к".
=OFFSET(Sheet1!$A$1,1,0,MATCH(TRUE,INDEX(ISBLANK(Sheet1!$A:$A),0,0),0)-2)
Теперь вы можете изменить формулу в C1
чтобы она была такой (все еще вводится с помощью Ctrl+Shift+Enter)
=IF(MAX(IFERROR(SEARCH(Keywords,$B2),0))>0,"yes","no")
Предостережение № 1
SEARCH
регистр. Если вы хотите поиск с учетом регистра, замените его на FIND
.
Предостережение № 2
Формула для динамического именованного диапазона предполагает, что в списке ключевых слов нет пробелов. Он будет ссылаться на все ячейки от A2
до ячейки перед первым пробелом. Конечно, это также предполагает, что где-то в A:A
есть пробел, что означает, что ваш список ключевых слов должен иметь длину менее 1 048 575 элементов.
Предостережение № 3
Вы можете сделать это без указанного диапазона, но ваша формула становится длиннее и сложнее следовать. Тем не менее, он сохраняет все это в одной формуле. Если ваши данные очень большие, я бы порекомендовал именованный диапазон, поскольку это ускорит вычисления. Чтобы объединить все это, формула в C2
будет
=IF(MAX(IFERROR(SEARCH(OFFSET(Sheet1!$A$1,1,0,MATCH(TRUE,INDEX(ISBLANK(Sheet1!$A:$A),0,0),0)-2),$B2),0))>0,"yes","no")