EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Excel Excel Resources Excel Tools Compare Two Lists in Excel

Compare Two Lists in Excel

Soumyashree Das
Article bySoumyashree Das
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated May 29, 2023

Compare Two Lists in Excel

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 tables? MS Excel allows comparing two lists or columns (Compare Two Lists in Excel) to verify if there are any common value(s) in both lists. Comparing two sets of lists may vary as per the situation. Using MS Excel, we can match two data sets and verify whether there is any common value in both sets. Excel does calculations but is useful in various ways like comparing data, data entry, analysis, visualization, etc. Below is an example that shows how data from two tables are compared in Excel. We’ll check each value from both data sets to verify common items in both lists.

Start Your Free Excel Course

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.

Compare two lists in excel 1-1

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.

Compare two lists in excel 1-2

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

Compare two lists in excel 1-3

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.

compare two lists 1-1

Example #2 – Match Data using the Row Difference Technique

To demonstrate this technique, we’ll use the same data as above.

Compare two lists in excel 2-1

First of all, the entire data is selected.

Compare two lists in excel 2-2

Then by pressing the F5 key on the keyboard, the “Go to special” dialog box opens. Then go to Special, as shown below.

Compare two lists in excel 2-3

Now, select “Row difference” from the options and press OK.

Compare two lists in excel 2-4

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

Compare two lists in excel 2-5

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

Compare two lists in excel 2-6

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.

Row Difference using IF Condition 3-1

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

Row Difference using IF Condition 3-2

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.

compare two lists 2

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.

Matching Data in case of Row Difference 4-1

Now we’ll apply the V-Lookup function to get the result in a new column.

Matching Data in case of Row Difference 4-2

After applying the formula, the output is shown below.

Matching Data in case of Row Difference 4-3

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.

Matching Data in case of Row Difference 4-4

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.

Matching Data in case of Row Difference 4-5

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.

Highlighting Matching Data 5-1

Next, we have to go to Conditional formatting > Highlight cell rules > Duplicate values, as shown below.

Highlighting Matching Data 5-2

Then a dialog box appears as below.

Highlighting Matching Data 5-3

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.

Highlighting Matching Data 5-4

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.

Highlighting Matching Data 5-5

Now, the result is shown below.

Highlighting Matching Data 5-6

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.

Partial Matching Technique 6-1

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

Partial Matching Technique 6-2

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.

compare two lists 3

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.

Recommended Articles

This is a guide to Compare Two Lists in Excel. We discuss How to Compare Two Lists in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. Excel Compare Two Columns
  2. Compare Dates in Excel
  3. Compare Text in Excel
  4. Compare Two Columns in Excel for Matches
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Compare Two Lists Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Compare Two Lists Excel Template

EDUCBA

डाउनलोड Compare Two Lists Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW