WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Compare Two Columns in Excel for Matches

Compare Two Columns in Excel for Matches

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

How to Compare Two Columns in Excel for the Matches?

There are several methods we have in excel to compare two columns in excel for the matches. It depends on data structure and your excel skills also. But let us try some of them in this article to get to know the concept of comparing two columns.

You can download this Compare to Match Two Columns Excel Template here – Compare to Match Two Columns Excel Template

Example #1 – Simple Method

For example, look at the below data in two columns. Now we need to match below two-column data; first, copy the above table to the worksheet in excel.

Excel Compare Two Columns - Example 1

  • The first method we can use to match this two-column is to put an equal sign in cell C2 and choose A2 cell.

Excel Compare Two Columns - Example 1-1

  • Since we are testing whether column 1 value is equal to column 2, put the equal sign, and choose B2 cell.

Excel Compare Two Columns - Example 1-2

This formula checks whether the A2 cell value is equal to the B2 cell value; if it is matching, it will show the result as TRUE, or else it will show the result as FALSE.

  • Hit the enter key and apply the excel formula to other cells also to get the matching result in all the cells.

Excel Compare Two Columns - Example 1-3

The first result is “FALSE” this is because, in cell A2, we have a value of “Colomobo,” and in B2 cell, we have a value of “Colambo” since both these values are different, we got the matching result is FALSE.

The next result is TRUE because, in both, the values of the cell are “Bangalore,” the matching result is TRUE.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

The next matching result is FALSE, even though both the cell values are looking similar. In these cases, we need to dig deep to find the actual variance between these two cell values.

First, I will apply the LEN function to find the number of characters with both the cells.

Excel Compare Two Columns - Example 1-4

On cell A4, we have six characters, and in cell B4, we have seven characters. This is because there may be extra leading space characters, so to deal with these scenarios, we can use the TRIM function while applying a matching formula. Enclose cell references in the TRIM function.

Excel Compare Two Columns - Example 1-5

Now, look at the result in the C4 cell. It is showing TRUE as a result, thanks to the TRIM function. The TRIM function removes all the unwanted space characters from the selected cell reference, and space could be in three ways trailing space, leading space, and extra space.

Example #2 – Match Case Sensitive Values

We can also match two column values based on a case sensitive nature as well, i.e., both the cell values should be a case sensitive match.

To match two column values based on case sensitivity, we need to use the excel’s built-in function “EXACT” function.

I have modified the above data as below.

Example 2

  • Open Exact function, and for Text 1 argument, choose the A2 cell.

Example 2-1

  • For the Text 2 argument, choose the B2 cell.

Example 2-2

  • Close the bracket and hit the enter key to get the result.

Example 2-3

The first one is FALSE because both the cell values are different. The second one is FALSE even though both the cell values are the same; this is because the letter “G” in the second value is in the uppercase letter, so the EXACT function looks for exact values and gives the result as FALSE if there is any deviation in character cases.

Things to Remember

  • Matching two cells is easy by inserting the operating symbol equal (=) sign in between two cells.
  • We can match only two columns at a time.
  • EXACT function matches two cell values but also considers the case sensitive characters of both the cells.

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 exact matches using TRIM and EXACT function along with examples and a downloadable template. You may also look at these useful functions in excel –

  • Compare Two Columns Using Vlookup
  • How to Compare Two Columns in Excel?
  • Excel Match Multiple Criteria
  • Index Match Multiple Criteria Rows and Columns
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

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

Login

Forgot Password?

WallStreetMojo

Download Compare to Match Two Columns Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More