Как определить, содержит ли ячейка какое-либо из значений в столбце

Question

Мой вопрос противоположен (обратный?) Как идентифицировать ячейки в столбце A, которые содержат любое из значений в столбце B. В частности:

У меня есть список из 19 000 имен и 10 000 паролей. Я пытаюсь определить пароли, которые содержат имена.

Например, пароли "Мэри" и «1Мар!"оба должны вернуть True.

С помощью этой формулы только точные совпадения были отмечены как True:

=If(Iserror(Match("*"&B1&"*",A:A,0)),"False","True")

Я хотел сделать следующее, но это не сработало:

=If(Iserror(Match(B1,"*"&A:A&"*",0)),"False","True")

[где B1 - пароль, а A:A - список имен].

Answer 1

Вы можете использовать FIND вместо MATCH в формуле массива. FIND возвращает номер символа, в котором найден ваш поисковый термин, или ошибку, если он не найден. Используйте следующее.

=SUMPRODUCT(NOT(ISERROR(FIND($A$1:$A$19000,B1)))*1)>0

SUMPRODUCT предназначен для того, чтобы эта функция работала как функция массива, не требуя какого-либо специального метода ввода.

Примечание. Для правильной работы необходим точный диапазон имен в формуле. Если это будет проблемой, вы можете использовать следующее:

=SUMPRODUCT(NOT(ISERROR(FIND(A:A,B1)))*1,NOT(ISBLANK(A:A))*1)>0

Answer 2

Вы были близки  Возьмите то, что вы пробовали, и измените порядок или параметры:

=IF(ISERROR(MATCH("*"&A:A&"*",B1,0)), "False", "True")

Теперь ограничьте (привязайте) его фактическим списком имен, а не всем столбцом A :

=IF(ISERROR(MATCH("*"&A$1:A$19000&"*",B1,0)), "False", "True")

и добавьте функцию AND() :

=IF(AND(ISERROR(MATCH("*"&A$1:A$19000&"*",B1,0))), "False", "True")

и нажмите Ctrl+Shift+Enter в конце, чтобы он стал формулой массива и отображался в фигурных скобках:

{=IF(AND(ISERROR(MATCH("*"&A$1:A$19000&"*",B1,0))), "False", "True")}

и перетащите / заполните.

PS Если вы действительно не хотите генерировать эти строки (или вы действительно хотите генерировать другие строки, например, «плохие» и «хорошие»), вы можете упростить:

=NOT(AND(ISERROR(MATCH("*"&A$1:A$19000&"*",B1,0))))

^{(Я написал это до того, как увидел ответ Excellll.)}