Trending September 2023 # Compare Two Lists In Excel # Suggested October 2023 # Top 14 Popular

You are reading the article Compare Two Lists In Excel updated in September 2023 on the website Dacvumuahe.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Compare Two Lists In Excel

Introduction to Compare Two Lists in Excel

Excel functions, formula, charts, formatting creating excel dashboard & others

How to Compare Two Lists in Excel?

Let’s understand how to compare two lists in Excel with a few examples.

You can download this Compare Two Lists Excel Template here – Compare Two Lists Excel Template

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, it will show TRUE; otherwise, it will show FALSE. We’ll use the Equal sign operator for the same.

After using the above formula, the 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 to C6 to get the result automatically.

Example #2 – Match Data using the 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 OK.

Now, matching cells are white, and unmatched cells are white and grey, as shown below.

We can highlight the row difference values for different colors at our convenience.

Example #3 – Row Difference using IF Condition

If the condition states that there is any match in the row. If there’s a match, the result will be “Matching” or “Not Matching”. The formula is shown below.

After using the above formula, the output is shown below.

Here A2 and B2 values don’t match, so that the result will be “Not Matching”. Similarly, other rows can result 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 always accurate 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, the 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 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 rows, 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 conditional formatting. First, we’ll have to highlight the data.

We can choose a different color from the drop-down list or stick with the default one, as shown above. Now the result can be seen 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 the Duplicate values dialog box, we’ll select “Unique” and then press 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, formulas or matching techniques won’t work here if we have “India is a country” in List 1 and “India” in List 2. Because List 2 has partial information about List 1. In such cases, the special character “*” can use. Below are two lists with company names with their revenue.

Here, we’ll apply V-Lookup using the special character “*” shown below.

Now, we can see that 1000 is printed in cell E2. We can also drag the formula till cell E6 to the result in other cells.

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 the case of organized data.