EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

Compare Two Columns in Excel for Matches

By Madhuri ThakurMadhuri Thakur

Home » Excel » Blog » Excel Tips » Compare Two Columns in Excel for Matches

Compare Two Columns in Excel for Matches

Excel Compare Two Columns for Matches (Table of Contents)

  • What is Comparing Two Columns in Excel for Matches?
  • Methods to Compare Two Columns for Matches

Introduction to Compare Two Columns in Excel

Excel is the most potent tool on the planet, and we all will agree with the fact. We can do almost everything using Excel when it comes to data analysis. It is not only a tool that allows you to store the data but more than that. When it comes to comparing and matching different data sets or, for that sake, different columns, we have several options to do. Comparing two or more columns in Excel for Matches is also a task for every data analyst, and in this article, we are going to see different methods using which we can compare two columns in Excel for matches.

Start Your Free Excel Course

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

What is Comparing Two Columns in Excel for Matches?

When we are doing data analysis in Excel, one of the most important tasks is comparing two columns and figuring out whether the values within them match row by row. This has importance based on the situation. Sometimes, you just want to truncate your dataset, and while doing that, it is of your core interest to reduce the duplication. You may want to check if the values in the two columns are matching. If it does, you may opt to delete one of the two columns. Or, you may be having some comparison and trying to see if that matches between the two columns from two different datasets for with the same properties. This way or that, you may need some methods to compare two columns for matches. We will walk you through some of the methods to achieve this.

Methods to Compare Two Columns for Matches

We will discuss some methods to compare two columns for matches.

You can download this Compare Two Columns for Matches Excel Template here – Compare Two Columns for Matches Excel Template

Method #1

Consider data as shown in the screenshot below, which includes different text values.

Compare Two Columns in Excel for Match 1-1

To check whether two of the columns are matching or not, we can use the simplest method. Follow the steps below:

Step 1: In cell C2, start initiating the formula using equals to sign (“=”). You can use your keyboard button for the same.

Popular Course in this category
Sale
All in One Excel VBA Bundle (120+ Courses)120+ Online Courses | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (2,356 ratings)
Course Price

View Course

Related Courses
Excel Training (21 Courses, 9+ Projects)Excel Data Analysis Training (15 Courses, 8+ Projects)Excel for Finance Training (18 Courses, 7+ Projects)

Compare Two Columns in Excel for Match 1-2

Step 2: Since we wanted to compare the value present in cell A2, use A2 as the first argument under C2 after the equals to sign.

Compare Two Columns in Excel for Match 1-3

Step 3: Now, we wanted to check if the value present in A2 is matching with the value present in B2; for that, use =B2 as the next argument. The entire formula will read as =A2=B2.

Compare Two Columns in Excel for Match 1-4

This is a logical formula that checks whether the value present in cell A2 is exactly matching with the value present in cell B2. If both values match, then it will return Boolean output TRUE. If both values don’t match, it will return Boolean output FALSE.

Compare Two Columns in Excel for Match 1-5

Now, drag the formula across all the working cells and see the output as shown below:

Compare Two Columns in Excel for Match 1-6

  • You can see some of the cells will match with each other from both of the columns, and some of those will not. This is because, for output in C2, A2 has the value “Sweets”, whereas the value in B2 is “swetes”. Same as with the 4th output where A4 has value as “Google” and B4 has “Googl”. Etc.
  • Note that this criterion for matching does not consider whether the text is in the upper or lower case.
  • There are sometimes extra spaces that allow the text to not match using the equality operator. For Example, see the fifth row where two strings are looking exactly similar, and they should match with each other. But the result is FALSE because we have an extra spacing at the end of cell B5. You can validate this by using the Len function as well.

This is one method using which we could check whether the values under two columns have a match or not. Let’s see a different method to check the same.

extra spaces

Method #2

Now, we will see a different method where we are going to use a conditional IF statement to check whether two of the columns have matches. Follow the steps below.

Sometimes, just getting values as TRUE/FALSE might not be a good representative when we are comparing two columns for matches. Having a proper phrase that can let you know whether the data is matching or not matching will be helpful in such cases. Let’s create a custom formula using a conditional IF statement that works in our favor.

Step 1: Consider the same example as the previous one. In cell C2, initiate a formula using a conditional IF statement as shown below:

IF Compare 2-1

Step 2: The first argument under conditional IF needs a logical test. In our example, we need to check whether two of the columns have matching values or not; therefore, we can use A2=B2 as a logical test under the IF statement. See the screenshot below:

IF Compare 2-2

You need to use a comma as a separator to separate multiple arguments of the IF statement.

Step 3: Now, we need to specify value for the argument [value_if_true]. This should be the value that will reflect in cell C2 if the logical test has output as TRUE. We will use the keyword “Matching” as a value for this argument.

IF Compare 2-3

Step 4: On similar lines, we must specify a value for the argument [value_if_false]. This will be the value being reflected in cell C2 if the logical condition has output as FALSE. We will use “Not Matching” as a value for this argument.

IF Compare 2-4

Don’t forget to use the double quotes within which the values should be added. Otherwise, there would be an error with the formula.

Step 5: Use closing parentheses to complete the formula and press Enter button to see the output.

IF Compare 2-5

You can see the value we are getting under C2 is “Not Matching” because the text in two columns is not matching with each other.

Step 6: Drag the formula across the rows to see whether two columns are having matching values with each other or not.

IF Compare 2-6

These are the two methods using which we can compare two columns in Excel for matches. We will end this article here and have some points to be remembered for you guys on the same.

Things to Remember

  • You can use the equality operators the same way we use them to compare two values in general mathematics. If the values do match, it will return TRUE as output, and if values do not match, it will return FALSE.
  • The equality operator doesn’t count the case of the text. Ex. DUBAI, Dubai, dubai are all the same for this operator.
  • We can also customize the results by simply using a conditional IF statement using which we can get a decent message to pop up for matches and non-matches instead of TRUE and FALSE.
  • We can compare data that has either numeric values or Mixed values in it from two different columns for matches on similar lines.

Recommended Articles

This has been a guide to Compare Two Columns in Excel for Matches. Here we discuss How to Compare Two Columns in Excel for Matches along with practical methods and downloadable excel template. You can also go through our other suggested articles –

  1. Compare Two Lists in Excel
  2. Compare Dates in Excel
  3. COLUMNS Formula in Excel
  4. Matching Columns in Excel

All in One Excel VBA Bundle (120+ Courses)

120+ Online Courses

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Excel Functions
  • Excel Tips
    • Recover Document in Excel
    • Excel Shortcut to Select Row
    • Excel Header Row
    • Excel Insert Button
    • Excel Translate
    • Budget in Excel
    • KPI Dashboard in Excel
    • Blank Invoice Excel Template
    • Write Formula in Excel
    • Auditing Tools in Excel
    • Autofit Row Height in Excel
    • Formatting Text in Excel
    • Add Rows in Excel Shortcut
    • Divide Cell in Excel
    • Compare Two Columns in Excel for Matches
    • Excel Troubleshooting
    • Unprotect Excel Workbook
    • Subtract Date in Excel
    • Carriage Return in Excel
    • Negative Numbers in Excel
    • Worksheets in Excel
    • Excel Match Multiple Criteria
    • Exponential Moving Average Formula
    • Sentence Case in Excel
    • Excel Reverse Order
    • Excel Timesheet Template
    • Weighted Average in Excel
    • CTRL Shift-Enter in Excel
    • Excel Expense Tracker
    • INDEX MATCH Function in Excel
    • Percent Change in Excel
    • Drag and Drop in Excel
    • Shade Alternate Rows in Excel
    • Excel Database Template
    • Inverse Matrix in Excel
    • New Line in Excel Cell
    • Format Cells in Excel
    • Excel Text with Formula
    • Project Management Template in Excel
    • Vlookup vs Index Match
    • Embedded in Excel
    • Count Colored Cells in Excel
    • Excel Hacks
    • Column Header in Excel
    • Database Function in Excel
    • Excel Spell Check
    • Linear Interpolation in Excel
    • Convert Excel to CSV
    • Excel Not Responding
    • Delta Symbol in Excel
    • Excel Mortgage Calculator
    • Excel Show Formula
    • Break Links in Excel
    • VLOOKUP Examples in Excel
    • Alternate Row Color Excel
    • Checklist in Excel
    • Microsoft Office Tips
    • Excel vs Access
    • Excel Change Case
    • Count Characters in Excel
    • Formula Bar in Excel
    • Invoice Template in Excel
    • Find and Replace in Excel
    • PowerPivot in Excel
    • Wildcard in Excel
    • Line Break in Excel
    • NULL in Excel
    • Text Formula in Excel
    • Family Tree in Excel
    • Insert Calendar in Excel
    • Page Setup in Excel
    • Search For Text in Excel
    • Switching Columns in Excel
    • 3D Cell Reference in Excel
    • Roadmap Template in Excel
    • Cell References in Excel
    • Excel Automation
    • Matching Columns in Excel
    • Evaluate Formula in Excel
    • How to Find Mean in Excel
    • How to Add Cells in Excel
    • Project Timeline in Excel
    • TODAY Formula in Excel
    • Poisson Distribution in Excel
    • YEAR Formula in Excel
    • REPLACE Formula in Excel
    • Create Spreadsheet in Excel
    • SEARCH Formula in Excel
    • HYPERLINK Formula in Excel
    • Pivot Table with Multiple Sheets
    • COLUMNS Formula in Excel
    • DAY Formula in Excel
    • RIGHT Formula in Excel
    • INDIRECT Formula in Excel
    • Excel IRR Formula
    • Pivot Table Sort
    • OR Formula in Excel
    • Pivot Table Formula in Excel
    • How to Edit Drop Down List in Excel?
    • WEEKDAY Formula in Excel
    • Chart Wizard in Excel
    • Time Difference in Excel
    • Lookup Table in Excel
    • SUBTOTAL Formula in Excel
    • LOOKUP Formula in Excel
    • Themes in Excel
    • Group Worksheets in Excel
    • Excel ISNUMBER Formula
    • 3D Maps in Excel
    • VLOOKUP Tutorial in Excel
    • Moving Columns in Excel
    • Grouping Columns in Excel
    • Excel Formula of Percentage
    • Excel Leading Zeros
    • Timesheet in Excel
    • Nested IF Formula in Excel
    • SUMIF Formula in Excel
    • Concatenation in Excel
    • Excel Insert Page Break
    • LEN Formula in Excel
    • Insert Comment in Excel
    • Excel Print
    • Fractions in Excel
    • Excel Add a Column
    • Borders in Excel
    • Excel Sum by Color
    • Excel vs Google Sheets
    • Calculate Percentage Increase in Excel
    • Excel Format Phone Numbers
    • Excel Search Box
    • Excel vs CSV
    • Excel vs Numbers
    • Excel Concatenate Date
    • Excel Bullet Points
    • Calculate Compound Interest in Excel
    • Find External Links in Excel
    • Excel Comma Style
    • Excel Remove Leading Spaces
    • Combine First and Last Name in Excel
    • How to Calculate Ratio in Excel
    • SUMPRODUCT Function with Multiple Criteria
    • Create Excel Template
    • Excel Greater than or Equal
    • Excel Keyboard Shortcuts
    • Excel Row vs Excel Column
    • Excel Operators
    • Excel Text Compare
    • Excel Uppercase Function
    • Excel Auto Numbering
    • Watermark in Excel
    • Excel Delete Row Shortcut
    • Excel Word Count
    • Row Count in Excel
    • Excel Grade Formula
    • Excel Percentage Difference
    • Excel Rows and Columns
    • Excel Group
    • Excel Move Columns
    • Excel Row Height
    • Excel Sparklines
    • Excel Drawing a line
    • Multiple IFS in Excel
    • Excel Strikethrough
    • Numbering in Excel
    • Share Excel Workbook
    • Excel Shortcut For Merge Cells
    • Excel OneDrive
    • Excel Track changes
    • Excel Hide Formula
    • Excel Filter Shortcuts
    • Excel 3D Reference
    • Protect Excel Workbook
    • Excel Moving Averages
    • Excel Row Limit
    • Excel Absolute Value
    • Excel Print Area
    • Excel Format Painter
    • Excel Separate text
    • Excel Autofit
    • Excel Columns to Rows
    • Excel Paste Shortcut Option
    • Excel Rows to Columns
    • Excel Insert Row Shortcut
    • Excel Extensions
    • Excel Unmerge Cells
    • Divide in Excel Formula
    • Excel Compare Two Columns
    • Not Equal To Excel
    • Excel Remove Duplicates
    • Excel Remove Spaces
    • Excel Calculate Age
    • Copy Excel Sheet
    • Excel Conditional Formatting Based on Another Cell Value
    • Excel Cell Reference
    • Excel Split Cell
    • Excel Circular Reference
    • Highlight Every Other Row in Excel
    • Errors in Excel
    • Excel Delete Pivot Table
    • Excel Pivot Table
    • Subtraction in Excel
    • Count Unique Values in Excel
    • Excel Highlight Duplicates
    • Excel Data Formatting
    • Random Numbers in Excel
    • Basic Excel Formulas
    • Page Numbers in Excel
    • Excel CAGR Formula
    • Excel Combine cells
    • IFERROR with VLOOKUP in Excel
    • Column Sort in Excel
    • Print Comments in Excel
    • Cheat Sheet of Excel Formulas
    • Remove Hyperlinks in Excel
    • Excel Drop Down List
    • Remove (Delete) Blank Rows in Excel
    • Relative Reference in Excel
    • Subscript in Excel
    • Superscript in Excel
    • Excel Insert Multiple Rows
    • Excel Absolute Reference
    • Unhide Columns in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (34+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (23+)
  • Statistical Functions in Excel (58+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (21+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL COURSE
  • Online Excel Data Analysis Course
  • Excel for Finance Course
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

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

Let’s Get Started

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

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

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

EDUCBA

Download Compare Two Columns for Matches Excel Template

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.

Special Offer - All in One Excel VBA Bundle (120+ Courses) Learn More