Find Occurrence of Text in a Column through Conditional Formatting

Question asked by user @ Excel forum:-

How to find occurrence of text in a column through Conditional Formatting?

I have 2 workbooks. In workbook (1), I have a column with a few thousand store names. In workbook (2), I have a column with a few hundred store names. The store names are not identical but have the following unique words/numbers: ABC123 etc. I want to identify if a store in workbook (2) exists in workbook (1). I have attached a sample file.


image 1


In Workbook 2, we have 2 columns, in Which column A contains only series number and in Column B we will get the whole product name through series number, which is common in both data.

To return the whole product number in workbook 2 use below given formula:-

  • =IFERROR(IF(G2="","",INDEX($B$2:$B$8,MATCH("*"&G2&"*",$A$2:$A$8,0))),"")
  • Press Enter.
  • Copy the same formula and paste in the range.

image 2


To put the “Conditional Formatting” follow below given steps:-

  • When we will click on “Conditional Formatting” drop down list will appear, where we have to select “Manage Rules”  dialog box will get open.
  • Where we have to click on “New Rule”, New Formatting Rule dialog box will get appear, click on Use a formula to determine which cells to format.
  • =COUNTIF($H$2:$H$8,A2)>0
  • Click on Format and select the green color.

image 3


  • Click on Apply and then on ok.

image 4


Main Page

Conditional Format Based on Dates

Find Occurrence of Text in a Column

How to Highlight a row on the basis of Cell

Compare 2 Columns and Return Fill Red if is different

How to check the row and then highlight the first cell of the row

Highlight Cells Tomorrow Excluding Weekend

Conditional Formatting to Mark Dates on a Calendar

How to apply Conditional Formatting  in a Cell before a Particular Character

Highlight the Top 10 Sales through Conditional Formatting

Conditional Formatting for Pivot Tables

Conditional Format Between First and Last Non-Blank Cells





Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube