Conditional Formatting if Cell matches any string in array.

[Daniel-Joseph Griffin]

Hey Forum.

From A5:A100 I have a list of strings. If I type a string into D5 and it doesn't match any string in the array, I want to conditionally format that cell to Red/Bold Text. But for the life of me I cannot work out how to accomplish this.

This is what I'm currently trying:

=COUNTIF(A5:A100,D5) < 1

I'm using Excel 365.

Thank you for the help.

 

[Vivek D]

Check attached.

Used VLOOKUP formula in another cell to check if the string entered exists or not and then setup a conditional format by checking if entered cell equals the other cell. If it doesn't match then it shows in Red Bold color.

 

[Luke M]

You were on right track with COUNTIF, just need to make the lookup range absolute reference I'm guessing.
Formula:
=COUNTIF($A$5:$A$100,D5)=0

In use:

 

[Daniel-Joseph Griffin]

Thank you both for the fast replies! -- Can't believe after all the tweaking I made to the formula all that was needed was to make it absolute, I'll have to remember that from now on.

 

[Luke M]

Glad to help.