Compare Two Lists in Excel to Highlight Matched Items

0 Flares 0 Flares ×

In this post we will look at how you can compare two lists in Excel to highlight matched or unmatched items. We will first identify the items that appear in both lists, and then look at how to highlight the items that appear in the first list but are missing from the second list.

The Match function will be used to compare both lists and will return if a record is found or is missing. The Match function returns the relative position of an item in a list. If it cannot find the item it will return the #N/A error message (learn more about the Match function).


Compare Two Lists to Highlight Matched Items

  1. Select the range of cells in the list you want to format.
  2. Click the Conditional Formatting button on the Home tab of the Ribbon and select New Rule from the list.
  3. Click the Use a formula to determine which cells to format option and enter the formula below in the box provided.

Because we are applying the Conditional Formatting to the entire row, the dollar signs are important to fix different elements of the cell references.

Using the Match function

Compare two lists using the Match function

  1. Click the Format button and select the formatting of your choice.
  2. Click Ok

Highlighting duplicate items in a list

Highlight the Missing Items

Instead of highlighting the duplicate items when we compare two lists, this function can be altered slightly to highlight the items that are unique to the first list.

Follow the steps as before but use the formula below for the Conditional Formatting rule.

ISNA with Match function

The ISNA function is used to return true if the #N/A error message is returned. Because #N/A is returned if the Match function cannot find a record, this formula identifies the missing items in a list.

Highlight the missing items

7 thoughts on “Compare Two Lists in Excel to Highlight Matched Items

  1. This is great however I have some values that repeat in column 1 but only appear once in column 2. With this formula both the repeating values in column 1 highlight but there is only one matching value in column two. Is there a way excel can only highlight if there is the same number of the repeating values in both columns? TIA

    • You can create a second rule and either use the duplicate Values rule that is provided with Conditional Formatting on that column with the duplicates. Or use the formula =COUNTIF($A$2:$A$100,$A2)>1 ssuming the range ia A2:A100.

  2. I have tried many times now. Please help! No matter which side range sells I selected, after formula it just hightlighted the whole column, even if some of number is missing. Is any procedure I have missed ? Thank you!

Leave a Reply

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