EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Compare Two Lists in Excel
Secondary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE

Compare Two Lists in Excel

By Soumyashree DasSoumyashree Das

Compare Two Lists in Excel

Compare Two Lists in Excel (Table of Contents)

  • Introduction to Compare Two Lists in Excel
  • How to 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 the table? MS Excel allows comparing two lists or columns 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 sets of data and verify whether there is any common value in both sets or not. Excel does calculations but is useful in various ways like the comparison of data, data entry, analysis, 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.

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.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,412 ratings)
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, then it will show TRUE; else, 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 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 on OK.

Compare two lists in excel 2-4

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

Compare two lists in excel 2-5

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

Compare two lists in excel 2-6

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.

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 accurate always 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 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.

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 basically 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 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.

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 on 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, 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.

Partial Matching Technique 6-1

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

Partial Matching Technique 6-2

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.

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. Here we discuss How to Compare Two Lists in Excel along with 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
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • 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

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

EDUCBA
Free Excel Course

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

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

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

EDUCBA Login

Forgot Password?

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

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

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

EDUCBA

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

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

Let’s Get Started

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

EDUCBA

Download Compare Two Lists Excel Template

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