Conditional Formatting - Formula to search values

Today we are looking at another example of using formulas in conditional formatting.

It is recommended to first read and understand the entrance Conditional Formatting - formulas for selecting cells.

Imagine you have the list of European football teams, located in the range B5:B31.

What we want to achieve is to enter some text in a cell, for example D2, and if that text is in any value from the list then highlight the cell(s) in the way you want.

To do that the formula to be used is:

=AND($D$5<>"",SEARCH($D$5,B6))

This formula compares the text located in the search cell ($D$2) to the range containing the data. The comparison is made through the SEARCH function that compares two values without distinction as to uppercase or lowercase. Finally, the intent of the function AND is to prevent that the format is applied to empty cells.

Thus if the search string is say 'Rea' or 'rea', the calculated result is the following.

Download an example file

To download an example file with all the examples that we are covering here click on the following link:

Conditional Formatting - search values

To go to the menu tutorial on conditional formatting, click here.

Subscribe and get all entries via email

You can do it by providing your email into "Join the newsletter" box in the right sidebar.

Or follow me via Social Media

Facebook, Twitter and Google+