How to Compare Two Lists in Excel? (Top 6 Methods)
Below mentioned are the 6 different methods that are used to compare two lists of a column in excel for matches and differences.
- Method 1: Compare Two Lists Using Equal Sign Operator
- Method 2: Match Data by Using Row Difference Technique
- Method 3: Match Row Difference by Using IF Condition
- Method 4: Match Data Even If There is a Row Difference
- Method 5: Highlight All the Matching Data using Conditional Formatting
- Method 6: Partial Matching Technique
Now let us discuss each of the methods in detail with an example –
#1 Compare Two Lists Using Equal Sign Operator
- Step 1: In the next column immediately after the two columns insert a new column called “Status”.
- Step 2: Now put the formula in cell C2 as =A2=B2.
- Step 3: This formula test whether cell A2 value is equal to cell B2. If both the cell values are matched then we will get TRUE as the result or else we will get the result as FALSE.
- Step 4: Now drag the formula to cell C9 for the other values to be determined,
Wherever we have the same values in common rows we got the result as TRUE or FALSE.
#2 Match Data by Using Row Difference Technique
Probably you have not used the “Row Difference” technique at your workplace but today I will show you how to use this technique to match data row by row.
- Step 1: To highlight non-matching cells row by row, select the entire data first.
- Step 2: Now press the excel shortcut key “F5” to open the “Go to Special” tool.
- Step 3: Press F5 key to open this window. Now in the “Go-To” window press “Special” tab.
- Step 4: In the next window “Go To Special” choose the “Row Difference” option. After selecting “Row Difference” click on “Ok”.
We get the following result.
As you can see in the above window it has selected the cells wherever there is a row difference. Now fill some color to it to highlight the row difference values.
#3 Match Row Difference by Using IF Condition
How can we leave out IF condition when we want to match data row by row. In the first example we have got either TRUE or FALSE as the result but what if we need the difference result instead of default results of either “TRUE or FALSE”. Assume you need a result as “Matching” if there is no row difference and the result should be “Not Matching” if there is a row difference.
- Step 1: First open IF condition in cell C2.
- Step 2: Apply the logical test as A2=B2.
- Step 3: If this logical test is TRUE enter the result criteria. In this scenario result criteria is “Matching” and if the row is not matching then we need the result as “Not Matching”.
- Step 4: Apply the formula to get the result.
- Step 5: Now drag the formula to cell C9 for the other values to be determined,
#4 Match Data Even If There is a Row Difference
Matching data on row difference methods may not work all the time, the value may be in other cells as well, so we need to use different technologies in these scenarios.
Look at the below data now.
In the above image, we have two lists of numbers. We need to compare list 2 with list 1. So let’s use our favorite function VLOOKUP.
So if the data is matching then we get the number otherwise we got the error value as #N/A.
Showing error values doesn’t look good at all. Instead of showing the error let’s replace them with the word “Not Available”. For this use IFERROR function in excel.
#5 Highlight All the Matching Data
If you are not a fan of excel formulas then don’t worry we can still be able to match data without formula as well. Using simple conditional formatting in excel we can actually able to highlight all the matching data of two lists.
- Step 1: For this first select the data.
- Step 2: Now go to Conditional Formatting and choose “Highlight Cell Rules >> Duplicate Values”.
- Step 3: Now you will see the “Duplicate Cell Values” formatting window.
- Step 4: You can actually choose the different formatting colors from the drop-down list in excel. Select the first formatting color and press the ok button.
- Step 5: This will highlight all the matching data from two lists.
- Step 6: Just in the case instead of highlighting all the matching data if you want to highlight not matching data then in the “Duplicate Values” window choose the option “Unique”.
This will highlight all the non-matching values.
#6 Partial Matching Technique
We have seen the issue of not full or the same data in two lists. For example, if the list 1 data has “ABC Pvt Ltd” and in list 2 we have “ABC” only. In these cases, our all default formulas and tools don’t recognize at all. In these cases, we need to employ the special character asterisk (*) to match partial values.
In List 1 we have company name and revenue details. In List 2 we have company names but not exact value as we have in List 1. This is a tricky situation we all have faced at our workplace.
In such cases still, we can match data by using special character asterisk (*).
We get the following result.
Now drag the formula to cell E9 for the other values to be determined,
The wildcard character asterisk (*) was used to represent any number of characters so it will match the full character for the word “ABC” as “ABC Pvt Ltd”.
Things to Remember
- Using the above techniques of comparing two lists in excel depends on the data structure.
- Row by row matching is not the best suited if the data is not organized.
- VLOOKUP is the often-used formula to match values.
This has been a guide to compare two lists in excel. Here we discuss the top 6 methods to compare two-column lists for matches and differences in excel (Equal Sign Operator, Row Difference Technique, IF Condition, Partial Matching Technique, and etc) along with examples and downloadable excel template. You may learn more about excel from the following articles –