Compare Two Lists in Excel for Matches (All Methods and Uses)

Comparing two lists in Excel allows you to determine variation between two sets of data. You can compare two lists in Excel for matches to identify data, find duplicates, extract unique values, etc. to analyze a dataset efficiently.

In this article, we will explore how to compare lists in Excel for matches. We will start the article by explaining how to use the equal sign operator to compare two lists for matches. Then we will move on to discuss how to apply conditional formatting to compare lists. We will also use VLOOKUP and MATCH functions for the same purpose. We will wrap up by describing how to compare multiple columns for matches in Excel.

Compare two lists in excel for matches

How to Compare Two Lists for Matches in Excel

1. Using Equal Sign Operator

This method finds matches in the same row.

  • Select cell D5 and write the following formula.
  • If cell B5 has the same data as cell C5, the result will be TRUE, otherwise, it will be FALSE.
  • AutoFill the rest of Column D.

Use Equal Sign Operator

2. Applying Conditional Formatting

2.1. Using Excel’s Built-in Conditional Formatting Rule

In this method, we will highlight values that are present only once in two lists.

  • Select both lists and go to, Home >> Conditional Formatting >> Highlight Cells Rules >> Duplicate Values.

Go to Conditional Formatting

  • In the Duplicate Values box, select Unique Values with Yellow Fill with Dark Yellow Text, and click on the OK button.

 Select Unique values

  • As a result, the cells with unique values will be highlighted.

Unique values Highlighted

2.2. Creating a New Formatting Rule

  • Select List 1 and go to Home >> Conditional Formatting >> New Rule.

Go to New Rule

  • Select Use a formula to determine which Cells to format and insert the following formula in Edit the Rule Description.
  • Then click on Format.

Apply COUNIF Formula

  • Go to the Fill tab and change the fill color to yellow and click on the OK button.

 Choose Format Color

  • Consequently, the values in List 1 that don’t exist in List 2 will be highlighted.

Unique data Highlighted

3. Using Row Difference Method

  • Select both lists and press F5 to open the Go To dialog box.

Open Go to box

  • Then click on Special.

Click on Special

  • After that, select Row differences and click OK.

Select Row Differences

  • As a result, the cells in List 2 of rows having different data will be selected.
  • Now, go to Home tab >> Fill Color tool >> select Yellow color from Standard Colors group.

Choose a Color to Highlight

  • Use fill color to highlight these data properly.

Unique Values Highlighted

4. Using Helper Column

  • Add a third column named Status. Then apply the following formula in the first cell of this column.
=IF(B5=C5,"Match","Not a Match")
  • As a result, the rows having the same data will show Match as output, otherwise, it will return Not a Match.

 Use Helper Column

5. Inserting VLOOKUP Function

5.1. Finding Full Match

  • Select cell D5 and enter the formula given below.
=IFERROR(VLOOKUP(C5,$B$5:$B$10,1,0),"Not a Match")
  • If the data in List 2 is found in List 1, the output will be that data. If it is not found, the formula will return Not a Match.


5.2. Finding Partial Match

  • Apply the following formula in cell F5.
  • This formula will return the Sales value using the partial matches of List 2.

Partial Match

6. Using the MATCH Function to Match Data Even If There is a Row Difference

  • Select cell D5 and type the following formula.
=IFNA(MATCH(B5,$G$5:G10,0),"Not Found")
  • It will return the number of rows and where it finds a match, otherwise, it will return Not Found.

Use MATCH Function

How to Compare Multiple Columns in Excel

1. Find Matches in All Cells Within the Same Row

  • Click on cell E5 and insert this formula.
=IF(AND(B5=C5, B5=D5), "Complete match", "")
  • If all three columns in the same row have the same data, the result will be a Complete match.

Find Matches in Whole Row

2. Find Matches in Any Two Cells in Same Row

  • Select cell E5 and write down the following formula.
=IF(OR(B5=C5, C5=D5, B5=D5), "Match", "")
  • The output will be Match, if any two of the three columns of the same row contain the same data.

Find Matches in Any Two Cells

Things to Remember

  • If the lists are not organized, using row-by-row matching methods will not work.
  • The Row Difference method only highlights the cells in List 2.

Frequently Asked Questions

1. Can I compare more than two lists simultaneously in Excel?

You can compare more than two lists simultaneously in various ways. You can apply the Array formula or use Power Query to compare multiple lists simultaneously.

2. Is it possible to compare lists from different worksheets or workbooks?

It is possible to compare lists from different worksheets or workbooks. While using any formula to compare lists, you need to make sure to insert the correct worksheets and workbook references.

3. What are some common scenarios where comparing two lists is useful?

Comparing two lists can be very helpful while working with data. It can be used for identifying data, finding unique values and duplicates, removing duplicates, etc. It is also useful for updating a large dataset.

Download Practice Workbook

Download this practice workbook while reading this article.


Thanks for making it this far. I hope you found this article helpful. In this article, we have discussed how to compare two lists in Excel for matches. We have used equal operator, conditional formatting, VLOOKUP, and MATCH functions to compare two lists. We have also covered how to compare multiple lists in Excel. If you have any queries or recommendations regarding this article, feel free to let us know in the comment section below.

<< Go Back to | Excel Match | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!


Alif Bin Hussain
Alif Bin Hussain

Alif Bin Hussain earned a BSc in Civil Engineering from Bangladesh University of Engineering and Technology. As an engineering graduate, he has a deep passion for research and innovation. He loves to play with Excel. In his role as an Excel & VBA Content Developer at ExcelDemy, he not only solves tricky problems but also shows enthusiasm and expertise in handling tough situations with finesse, emphasizing his dedication to delivering top-notch content. He is interested in C, C++,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF