How to Compare Two Lists in Excel? (Top 6 Methods)
Below mentioned are the six 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
Follow the below steps to compare two list.
- In the next column, immediately after the two columns, insert a new column called “Status.”
- Now put the formula in cell C2 as =A2=B2.
- 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 a result, or else we will get the result as FALSE.
- 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 the F5 key to open this window. Now in the “Go-To” window, press the “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 the IF condition when we want to match data row by row. In the first example, we have got either TRUE or FALSE as a result, but what if we need the difference result instead of the 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, the result criteria are “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 the IFERROR function in excelIFERROR Function In ExcelWhen an error occurs in any formula or argument, the IFERROR excel function is used to display the result. This function can be used in conjunction with other functions to alert the user of any errors in the formula..
#5 Highlight All the Matching Data
If you are not a fan of excel formulasExcel FormulasThe term "basic excel formula" refers to the general functions used in Microsoft Excel to do simple calculations such as addition, average, and comparison. SUM, COUNT, COUNTA, COUNTBLANK, AVERAGE, MIN Excel, MAX Excel, LEN Excel, TRIM Excel, IF Excel are the top ten excel formulas and functions., 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 be 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, all our 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 the 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 a 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 a downloadable excel template. You may learn more about excel from the following articles –