Compare Two Lists in Excel (Table of Contents)
Introduction to Compare Two Lists in Excel
Data matching or comparison in different data sets is not new in data analysis today. SQL Join method allows joining two tables having similar columns. But how do we know that there are similar columns in both the table? MS Excel allows comparing two lists or columns to verify if there are any common value(s) in both the lists. Comparing two sets of lists may vary as per the situation. Using MS Excel, we can match two sets of data and verify whether there is any common value in both the sets or not. Excel not only does calculations but is useful in various ways like the comparison of data, data entry, analysis, and visualization, etc. Below is an example that shows how data from two tables are compared in Excel. Basically, we’ll check each value from both the data sets to verify common items present in both the lists.
How to Compare Two Lists in Excel?
Let’s understand how to compare two lists in Excel with a few examples.
Example #1 – Using the Equal Sign Operator
Below are two lists called List1 and List2 which we’ll compare.
Now, we’ll insert another column called “Result” to display the result as TRUE or FALSE. If there is a match in both cells in a row, then it will show TRUE else it will show FALSE. We’ll use the Equal sign operator for the same.
After using the above formula, output is shown below.
The formula is =A2=B2 which states cell A2 is compared with cell B2. A1 has “Raj” and B1 has “Ankita” which does not match. So, it will show FALSE in the first row of the result column. Similarly, the rest of the rows can be compared. Alternatively, we can drag the cursor from C2 till C6 to get the result automatically.
Example #2 – Match Data using Row Difference Technique
To demonstrate this technique, we’ll use the same data as above.
First of all, the entire data is selected.
Then by pressing the F5 key on the keyboard, the “Go to special” dialog box opens. Then go to Special as shown below.
Now, select “Row difference” from the options and press on OK.
Now, matching cells are in while color and unmatched cells in white and grey color as shown below.
We can highlight the row difference values for different colors as per our convenience.
Example #3 – Row Difference using IF Condition
If condition basically states if there is any match in the row. If there’s a match, the result will be “Matching” or else “Not Matching”. The formula is shown below.
After using the above formula, output is shown below.
Here A2 and B2 values don’t match, so the result will be “Not Matching”. Similarly, other rows can be resulted with the condition or alternatively, we can drag the cursor and the output will come automatically as below.
Example #4 – Matching Data in case of Row Difference
This technique is not accurate always as values may be in other cells too. So, different techniques are used for the same.
Now we’ll apply the V-Lookup function to get the result in a new column.
After applying the formula, output is shown below.
Here, the function states that B2 is being compared with values from List 1. So, the range is A2:A9. And the result can be seen as shown below.
If 160466 is there in any cell in List 1 then, 160466 will be printed using V-Lookup. Similarly, the rest values can be checked. In the 2nd and 5th row, there is an error. It is because values 183258 and 356160 are not present in List 1. For that, we can apply the IFERROR function as follows. Now, the result is finally here.
Example #5 – Highlighting Matching Data
Sometimes, we feel fed up with Excel formulas. So, we can use this method to highlight all the matching data. This method is basically conditional formatting. First, we’ll have to highlight the data.
Next, we have to go to Conditional formatting > Highlight cell rules > Duplicate values as shown below.
Then a dialog box appears as below.
We can either choose a different color from the drop-down list or stick with the default one as shown above. Now the result can be seen clearly below.
Here common values are highlighted in red color while unique values are colorless. We can color only unique values if we need to find unmatched values. For that, instead of selecting “duplicate” in Duplicate values dialog box, we’ll select “Unique” and then press on OK.
Now, the result is shown below.
Here, only unique and unmatched values are highlighted in red.
Example #6 – Partial Matching Technique
Sometimes both lists don’t have the exact data. For example, if we have “India is a country” in List 1 and “India” in List 2, then formulas or matching techniques won’t work here. Because List 2 has partial information of List 1. In such cases, the special character “*” can be used. Below are two lists with company names with their revenue.
Here, we’ll apply V-Lookup using special character “*” as shown below.
Now, we can see that 1000 is printed in cell E2. We can drag the formula till cell E6 to the result in other cells as well.
Things to Remember
- The above techniques depend on the data structure of the table.
- V-Lookup is the common formula to use when the data is not organized.
- Row by row technique works in case of organized data.
Recommended Articles
This is a guide to Compare Two Lists in Excel. Here we discuss How to Compare Two Lists in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
