SOLVED

Conditional Formatting "not containing" for multiple terms

%3CLINGO-SUB%20id%3D%22lingo-sub-2966285%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20%26amp%3Bquot%3Bnot%20containing%26amp%3Bquot%3B%20for%20multiple%20terms%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2966285%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20conditional%20formatting%20on%20a%20column%20that%20looks%20like%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDog%3C%2FP%3E%3CP%3EDog%3C%2FP%3E%3CP%3EDoog%3C%2FP%3E%3CP%3ECat%3C%2FP%3E%3CP%3EKat%3C%2FP%3E%3CP%3EElephant%3C%2FP%3E%3CP%3EElephant%3C%2FP%3E%3CP%3EElefant%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20case%20I%20would%20like%20%22Doog%22%2C%20%22Kat%22%2C%20and%20%22Elefant%22%20to%20be%20highlighted%2C%20aka%20anything%20that%20is%20a%20typo%20or%20does%20not%20fit%20my%20list%20of%20proper%20or%20standardized%20names.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20created%20a%20second%20sheet%20with%20the%20names%20that%20are%20standard%2C%20and%20anything%20that%20is%20NOT%20included%20on%20this%20list%20I%20would%20like%20highlighted%20in%20red.%20Is%20there%20a%20simple%20way%20to%20do%20this%3F%20The%20list%20has%20187%20unique%20names.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2966285%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2966340%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20%26amp%3Bquot%3Bnot%20containing%26amp%3Bquot%3B%20for%20multiple%20terms%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2966340%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1219118%22%20target%3D%22_blank%22%3E%40k8080f%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20list%20to%20be%20checked%20is%20in%20D2%3AD500%20on%20Sheet%201%2C%20and%20the%20list%20of%20standard%20names%20is%20in%20A2%3AA188%20on%20Sheet%202.%3C%2FP%3E%0A%3CP%3ESelect%20D2%3AD500%20on%20Sheet%201.%20D2%20should%20be%20the%20active%20cell%20in%20the%20selection.%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20click%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3CBR%20%2F%3ESelect%20'Use%20a%20formula%20to%20determine%20which%20cells%20to%20format'.%3CBR%20%2F%3EEnter%20the%20formula%3C%2FP%3E%0A%3CP%3E%3DISERROR(MATCH(D2%2C'Sheet%202'!%24A%242%3A%24A%24188%2C0))%3C%2FP%3E%0A%3CP%3EClick%20Format...%3CBR%20%2F%3EActivate%20the%20Fill%20tab.%3CBR%20%2F%3ESelect%20a%20highlight%20color.%3CBR%20%2F%3EClick%20OK%20twice.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2966391%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20%26amp%3Bquot%3Bnot%20containing%26amp%3Bquot%3B%20for%20multiple%20terms%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2966391%22%20slang%3D%22en-US%22%3EThank%20you!%3C%2FLINGO-BODY%3E
New 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 (New 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