SOLVED

Conditional Formatting "not containing" for multiple terms

Copper Contributor

Hello,

 

I am trying to use conditional formatting on a column that looks like

 

Dog

Dog

Doog

Cat

Kat

Elephant

Elephant

Elefant

 

In this case I would like "Doog", "Kat", and "Elefant" to be highlighted, aka anything that is a typo or does not fit my list of proper or standardized names. 

 

I created a second sheet with the names that are standard, and anything that is NOT included on this list I would like highlighted in red. Is there a simple way to do this? The list has 187 unique names. 

 

 

4 Replies
best response confirmed by k8080f (Copper Contributor)
Solution

@k8080f 

Let's say the list to be checked is in D2:D500 on Sheet 1, and the list of standard names is in A2:A188 on Sheet 2.

Select D2:D500 on Sheet 1. D2 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=ISERROR(MATCH(D2,'Sheet 2'!$A$2:$A$188,0))

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK twice.

Is there a way to specify that it is an error if it doesn't match the other column exactly? I noticed when the cells I would like highlighted for errors contains part of the reference column they do not get highlighted.

For example, if "Dog" is on my list of words that are ok, but I type "Dogs" and it is not recognized as an error, but I would like it to be considered an error.

Thank you!

@k8080f 

I don't understand - Dogs should be flagged as an error.

 

S0886.png

1 best response

Accepted Solutions
best response confirmed by k8080f (Copper Contributor)
Solution

@k8080f 

Let's say the list to be checked is in D2:D500 on Sheet 1, and the list of standard names is in A2:A188 on Sheet 2.

Select D2:D500 on Sheet 1. D2 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=ISERROR(MATCH(D2,'Sheet 2'!$A$2:$A$188,0))

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK twice.

View solution in original post