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.
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.
=B5=C5- 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.
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.
- In the Duplicate Values box, select Unique values with Yellow Fill with Dark Yellow Text , and click on the OK button.
- As a result, the cells with unique values will be highlighted.
2.2. Creating a New Formatting Rule
- Select List 1 and go to Home >> Conditional Formatting >> New Rule.
- Select Use a formula to determine which Cells to format and insert the following formula in Edit the Rule Description.
=COUNTIF($C$5:$C$10,B5)=0- Then click on Format.
- Go to the Fill tab and change the fill color to yellow and click on OK button.
- Consequently, the values in List 1 that don’t exist in List 2 will be highlighted.
Similar Readings
3. Using Row Difference Method
- Select both lists and press F5 to open the Go To dialog box.
- Then click on Special.
- After that, select Row differences and click OK.
- 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.
- Use fill color to highlight these data properly.
Similar Readings
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.
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.
=VLOOKUP("*"&E5&"*",$B$5:$C$10,2,0)- This formula will return the Sales value using the partial matches of List 2.
Similar Readings
6. Using 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 row and where it finds a match, otherwise, it will return Not Found.
How to Compare Multiple Columns in Excel
1. Find Matches in All Cells Within 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 Complete match.
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.
Similar Readings
- How to Return Row Number of a Cell Match in Excel
- Excel VBA: Copy Row If Cell Value Matches
- Excel VBA to Match Value in Range
- Excel VBA to Match String in Column
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 can 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.
Conclusion
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, 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.
