Excel Conditional Formatting based on match in another column

Tips, Tips & ShortcutsConditional Formatting for List of Partial MatchesFebruary 10, 2021Jon Acampora15 commentsBottom Line: Learn how to apply conditional formatting to cells

Excel Conditional Formatting based on match in another column

Tips, Tips & Shortcuts

Conditional Formatting for List of Partial MatchesFebruary 10, 2021Jon Acampora15 comments

Bottom Line: Learn how to apply conditional formatting to cells that contain a partial match to a list of values.

Skill Level: Intermediate

Watch the TutorialWatch on YouTube & Subscribe to our Channel

Download the Excel File

The Excel file that I use in the video can be downloaded here:Conditional-Formatting-Partial-Match-List-of-Values.xlsxDownload

Conditional Formatting for Partial Matches

Let's say you have a list of items and you want to highlight the ones that start with a certain character or series of characters. You can use a formula to search, find, and apply formatting based on a partial match against a list of data.

In my example today, I want to color cells for any Part Numbers that begin with a particular list of characters.

Format cells based on list of partial matches

Using the Conditional Formatting Window

To start, we're going to open up the Conditional Formatting window. You can access this by going to the Home tab on the Ribbon and selecting the Conditional Formatting drop-down menu. Then select New Rule.

New Rule for Conditional Formatting

For the Rule Type, choose the option that says Use a formula to determine which cells to format. That will open up a field where you can type or paste a formula. I prefer to write my formulas in a cell and then paste it in, but it's entirely up to you.

Formula determines conditional formatting

Writing the Formula

There are many different lookup formulas and ways to go about this, but we're going to use the COUNTIF function. There are two arguments to COUNTIF. The first is the range, and the second is the criteria.

For range, the formula is looking for the set of data that it should look through to find the criteria. In our case it is our list of 5-character codes that the part numbers should start with. That's F3:F5 on our worksheet. But we want to make those values absolute ($F$3:$F$5) since they won't be changing.

For the criteria, we want to look at the first five characters of the product number. To do that we will use the formula called LEFT. It looks at the leftmost characters up to the number of places you determine. To fill out the arguments for the LEFT function, you choose the first cell in the list (A3) as the text argument, and the number 5 for num_chars argument.

When we close our parenthesis, our complete formula reads:

=COUNTIF($F$3:$F$5,LEFT(A3,5))

When written in the cell, this function returns a value of 1 or 0 depending on if it found those five digits at the beginning of the entry. Any value other than 0 equates to TRUE in the Conditional Formatting window, while a 0 is the same as returning FALSE.

Applying the Formatting

One thing to note is that if you are writing your formula directly into the conditional formatting rule, if you select cell A3 instead of actually typing it, Excel is going to make it an absolute reference ($A$3) by default, which is not what we want.

Once the formula is copied or typed into the Conditional Formatting rule, click the Format button. This gives you all of the formatting options you can choose from that will apply when the formula is true.

Format cells when partial match is found

After hitting OK, the new formatting will appear in the cells where the partial match is true.

If you find that you've accidently applied the rule to the cell where you wrote the formula instead of the list of Part Numbers, you can easily make edits to that range by selecting Manage Rules in the Conditional Formatting drop-down on the Home tab. (That's an edit I often have to make because I forgot to highlight the range I wanted before opening up the rule manager.)

Applying Conditional Formatting to Entire Rows

If you want to expand your conditional formatting so it extends across the entire row, that's easy to do. As mentioned above, you can edit the existing rule by going to the Home tab, clicking the Conditional Formatting drop-down menu, and choosing Manage Rules. From there you can just change the range that the conditional formatting applies to.

Conditional Formatting Rules Manager

Click to enlarge

In order for this to work, the column reference in our formula must be absolute ($A3). This is because we want the formatting to be determined based on the values from the original column.

The criteria for the conditional formatting does not have to be on the same sheet as the cells being formatted, and if you want to add, change, or delete some of the criteria for the formatting, just ensure that your formula includes the updated range of cells.

Partial Matches that Aren't at the Beginning

What if the characters you are trying to match fall at the end of the entry instead of the beginning? For example, maybe you want to highlight any entries with the suffix yahoo.com in a column of emails. As you can probably guess, you'd simply use the RIGHT function instead of LEFT.

But there may be times when the data you are looking for isn't always in the front or the back of the entry. Or maybe you you're not sure where it would fall because you are not familiar with the entire list of data. For example, maybe you have a list of thousands of inventory product names and you want to call attention to how many contain the description chocolate and/or coffee anywhere in the name. For this type of partial match, we can use the COUNT and SEARCH functions.

The SEARCH function would look for the entries that contain the words or characters in your list. Then the COUNT function returns a value of 0 if no match is found and 1 (or more) if a match (or more than one match) is found. Here's an image showing how this formula looks.

COUNT and SEARCH functions to find partial matches

Conclusion

I hope this helps you to understand how to apply conditional formatting to cells based on partial matches. I've got several other posts that use conditional formatting that I invite you to check out:

  • How to Apply Conditional Formatting to Rows Based on Cell Value
  • Highlight Rows Between Two Dates with Conditional Formatting in Excel
  • How to Filter for Duplicates with Conditional Formatting
  • Progress Doughnut Chart with Conditional Formatting in Excel
  • How to Apply Conditional Formatting to Pivot Tables

If you have any questions about this process, leave a comment. Thanks for reading!Previous Compare Two Sheets for Duplicates with Conditional FormattingNext Quick Tip for Navigating Formula References with the Go To Window

You may also like

XLOOKUP Shortcuts

Shortcuts for Writing XLOOKUP and VLOOKUP Formulas

File Explorer Shortcuts

12 Keyboard Shortcuts for Windows File Explorer

Numbered Lists

4 Ways to Create Numbered Lists in Excel

Sort Before Deleting Rows

How to Prevent Excel from Freezing or Taking A Long Time when Deleting Rows

Diane Smith says:February 11, 2021 at 5:58 am

Couldnt you use Range Names in the conditional formatting formula? Wouldnt that make it easier to add to the data formated or criteria lists? Could you use a Table Name? Or will this mess up the Absolute reference $A2 cell?Reply

Jon Acampora says:February 11, 2021 at 11:10 am

Hi Diane,

Great idea! Yes, the named range for the criteria Table would work. It would not mess up the mixed reference for $A2.

The formula would look like the following if we created a named range rngCriteria for the Table column that contains the criteria values.

=COUNTIF(rngCriteria,LEFT($A3,5))

Then any new criteria added to the table would be automatically included in the conditional formatting.

Well update the post to include this technique.

Thanks again and have a nice day!Reply

Carl Walton says:February 11, 2021 at 8:42 am

Great Tip.

Also if you make the criteria a table (Control+T) you can add criteria and the conditional format will be updated automatically.Reply

Jon Acampora says:February 11, 2021 at 10:59 am

Great suggestion Carl! Thanks for sharing. Reply

Jon Acampora says:February 11, 2021 at 11:06 am

I should have mentioned that the Conditional Formatting Manager will not let you reference Tables (structured references) in formulas. However, Diane mentioned a workaround in her comment on creating the named range for the table. Then referencing the named range in the formula.Reply

David Hurley says:February 11, 2021 at 9:39 am

Do you also teach MS Access or have a website who teaches like you do?Reply

Jon Acampora says:February 11, 2021 at 11:12 am

Hi David,
I dont specifically teach Access at this time. We do have some training on how to connect to it with Power Query in our Elevate Excel Training Program. However, it does not cover how to use Access.

Unfortunately, I dont use Access often and cant recommend anyone that teaches it. Well see about adding more training on it in the future.

Thanks again and have a nice day!Reply

Steve says:February 11, 2021 at 11:23 am

Good topic choice, and excellent presentation. ThanksReply

Astha says:February 11, 2021 at 11:39 am

Thanks for these amazing videos.I am learning alot.Reply

Florent GAUTHIER says:February 13, 2021 at 1:31 am

Very good suggestion from Diane.
Another one could be to turn the criterion length (5 currently written in hard) into a more general one (LEN function). The only thing is that it has a constrain: all criteria items must be same length. Thanks again for this new video John.Reply

Jon Acampora says:February 16, 2021 at 10:29 am

Great suggestion Florent! Thanks for sharing!Reply

Chris Munter says:March 22, 2021 at 3:52 am

Hello

I love the COUNT(SEARCH conditional formatting. Using this one formula overcomes the need for creating multiple formulas to meet different search search requirements.

Could you please explain how I can prevent the relevant cells from picking up the formatting when the search criteria cell(s) are empty?

ThanksReply

Siddhartha says:May 6, 2021 at 3:58 am

I am facing a problem, I have written a formula =COUNTIF(carcgl!$A$2:$C$63830,@$A$2:$A$3820) to compare data in two column in two different sheets.

this formula is giving the value for XCYZ when it is finding XCYZ.ccy.com.

I just want that it should throw 0 for such situation. Please let me know what modification should I do with the formula.Reply

RAGHU says:May 26, 2021 at 6:08 am

how can i highlight names such as rama, shama, bhama at one short using conditional formatting in a table.Reply

Chris says:June 2, 2021 at 3:40 pm

What is working best for me is to create the table of criteria as a dynamic table in Name Manager & put that name in the formula. Then criteria can be added or removed from the table w/o ever having to edit the formula again.

If you look in Name Manager immediately after creating any table, in this case Criteria, youll see it already created a name for the table as Table1, Table2, etc. This is not the one you want in your formula. In this case, I would name the auto-created table as z_Criteria to throw it to the bottom of Name Manager. Then select any data cell in the table, open Name Manager, select add a NEW name, name it Criteria, hover over the table header cell top right until a black arrow down appears, then just click while the arrow is showing. Now you have your named dynamic table for use in the formula.Reply

More results...   Generic filters Hidden labelExact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.Join Our Free Newsletter

Free Excel Training Webinar Modern Power Tools

Video liên quan