How To Compare Two Lists In Excel Using Top 6 Methods

How to Compare Two Lists in Excel? (Top 6 Methods)

Below are the six different methods used to compare two lists of a column in Excel for matches and differences.

Method 1: Compare Two Lists Using Equal Sign OperatorMethod 2: Match Data by Using Row Difference TechniqueMethod 3: Match Row Difference by Using IF ConditionMethod 4: Match Data Even If There is a Row DifferenceMethod 5: Highlight All the Matching Data using Conditional FormattingMethod 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

Wherever we have the same values in common rows, we get the result as “TRUE” or “FALSE.”

Immediately after the two columns, we must insert a new column called “Status” in the next column. Now, we must put the formula in cell C2 as =A2=B2. This formula tests whether cell A2 value is equal to cell B2. If both the cell values are matched, we will get the result as “TRUE” or “FALSE.” We will drag the formula to cell C9 to determine the other values.

#2 Match Data by Using Row Difference Technique

You might not have used the “Row Difference” technique at your workplace. But today, we will show you how to use this technique to match data row by row.

Step 1: To highlight non-matching cells row by row, we must select the entire data first.
Step 2: Now, we must press the excel shortcut keyExcel Shortcut KeyAn Excel shortcut is a technique of performing a manual task in a quicker way.read more “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, we must go to the “Go To Special” and choose the “Row differences” option. Then, click on “OK.”

We will get the following result.

As we can see in the above window, it has selected the cells wherever there is a row difference. Therefore, we must fill in some colors 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.” But what if we need a different result instead of the default results of either “TRUE or FALSE.” Assume we 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, we must open the IF condition in cell C2.
Step 2: Then, apply the logical testLogical TestA logical test in Excel results in an analytical output, either true or false. The equals to operator, “=,” is the most commonly used logical test.read more as A2=B2.
Step 3: We must enter the result criteria if the logical test is “TRUE.” In this scenario, the result criteria are “Matching,” If the row does not match, we need the result as “Not Matching.”
Step 4: Next, we need to apply the formula to get the result.
Step 5: We must drag the formula to cell C9 to determine the other values.

#4 Match Data Even If There is a Row Difference

The matching data on the row differences method may not work all the time; the value may be in other cells too. So we need to use different technologies in these scenarios.

Now, look at the below data.

In the above image, we have two lists of numbers. We need to compare list 2 with list 1. So let us use our favorite function VLOOKUPFunction VLOOKUPThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more.

So, if the data matches, we get the number; otherwise, we get the error value as #N/A.

Showing error values does not look good. So instead of showing the error, let us replace them with the word “Not Available.” For this, use the IFERROR function in excelIFERROR Function In ExcelThe IFERROR function in Excel checks a formula (or a cell) for errors and returns a specified value in place of the error.read more.

#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.read more, do not worry. We can still match data without the formula. For example, using simple conditional formatting in excelConditional Formatting In ExcelConditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions. It can be found in the styles section of the Home tab.read more, we can highlight all the matching data of two lists.

Step 1: We must first select the data.
Step 2: Now, we must go to “Conditional Formatting” and choose “Highlight Cell Rules” » “Duplicate Values.”
Step 3: As a result, we can see the “Duplicate Cell Values” formatting window.
Step 4: We can choose the different formatting colors from the drop-down list in excelDrop-down List In ExcelA drop-down list in excel is a pre-defined list of inputs that allows users to select an option.read more. Select the first formatting color and press the “OK” button.
Step 5: This will highlight all the matching data from the two lists.
Step 6: Just in the case, instead of highlighting all the matching data, if we want to highlight not matching data, then we can go to the “Duplicate Values” window and choose the option “Unique.”

As a result, it will highlight all the non-matching values, as shown below.

#6 Partial Matching Technique

We have seen the issue of not having full or the same data in two lists. For example, if the List 1 data has “ABC Pvt Ltd.“ In List 2, we have “ABC” only. In these cases, all our default formulas and tools are not recognized. Therefore, we need to employ the special character asterisk (*) to match partial values in these cases.

In List 1, we have the company name and revenue details. In List 2, we have company names but not exact values as we have in List 1. It is a tricky situation that 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.

We will drag the formula to cell E9 to determine the other values.

The wildcard character asterisk (*) was used to represent any number of characters so that it will match the full character for the word “ABC” as “ABC Pvt Ltd.”

Things to Remember

The use of the above techniques for comparing two lists in Excel upon the data structure.If the data ends not organized, row by row matching is not the best suited.VLOOKUP is the often-used formula to match values.

How to Compare Two Lists in Excel? (Top 6 Methods)#

#1 Compare Two Lists Using Equal Sign Operator#

#2 Match Data by Using Row Difference Technique#

#3 Match Row Difference by Using IF Condition#

#4 Match Data Even If There is a Row Difference#

#5 Highlight All the Matching Data#

#6 Partial Matching Technique#

Things to Remember#

Recommended Articles#