Как выделить ячейку, которая не содержит конкретного текста из любого столбца

Когда ячейка содержит ошибку, подобную #VALUE!, это не текст с фразой «VALUE»; не то, что вы можете (или должны) искать таким образом. Это своего рода заполнитель, показывающий, где ошибка.

Чтобы определить, использует ли формула или функция ошибку, используйте ISERROR или IFERROR.

Например, если вы хотите вернуть Not Found, если ваша формула выдает ошибку, вы можете использовать:

=IFERROR(SEARCH($A:$A,'Sheet 2'!$A:$A),"Not Found")

Я предпочитаю VLOOKUP для поиска совпадений.

Например, вы можете ввести ячейку B2 на листе First:

=VLOOKUP("*" &A2&"*",second!$A$2:$A$5,1,FALSE)

... а затем заполнить или скопировать формулу до ячейки A7.

Если a совпадающая фраза найдена в Second, тогда она покажет эту фразу, иначе она вызовет ошибку.

На этот раз с использованием ISERROR (а также IF) в качестве примера вы можете отобразить, было ли совпадение, вместо этого, используя эту формулу в ячейке B2 на листе First :

=IF(ISERROR(VLOOKUP("*"&A2&"*",second!$A$2:$A$5,1,FALSE)),"No Match","Matched!")

... и затем заполните или скопируйте формулу до ячейки A7.

[/g3]

Дополнительная информация:

• TechOnTheNet: Как использовать функцию ISERROR
• ExcelJet: Как использовать функцию Excel IFERROR

Выберите столбец A на листе «Животные» и создайте CFR на основе этой формулы,

=isna(match("*"&$A1&"*", 'Sheet 2'!$A:$A, 0))

. Под «подстановкой» соответствия со значением на листе «Животные» вы создаете «содержит» в пределах 'критериев для любой строки в столбце A листа 2, как функция поиска по независному регистру.

Вместо того, чтобы создавать серию изображений о том, как сделать это вручную, это эквивалент VBA.

With worksheets("Animals").range("a:a")
    .FormatConditions.Delete
    with .FormatConditions.Add(Type:=xlExpression, Formula1:="=isna(match(char(42)&$A1&char(42), 'Sheet 2'!$A:$A, 0))")
        .Interior.Color = vbred
    end with
End With

Основываясь на вашем примере, это должно работать:

=if(A3="","",if(count(search(A3,second!$A:$A))>0,":-)",":-("))

ARRAY FORMULA нажмите SHIFT + CTRL + ENTER, чтобы ввести формулу

Вы можете разместить это

Вы можете использовать условное форматирование в списке, чтобы выделить как в моем случае «:-(» грустный смайлик, или вы используете более заметные буквы для выделения.

Если вы просто хотите выделить «пропавших» животных, то удалите счастливый смайлик из формулы.

=if(A3="","",if(count(search(A3,second!$A:$A))>0,"",":-("))

ФОРМА МАШИ нажмите SHIFT + CTRL + ENTER, чтобы введите формулу