WallStreetMojo

WallStreetMojo

WallStreetMojo

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

Compare and Match Columns in Excel

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

Compare Two Columns in Excel for Match

Comparing and matching two columns in excel data can be done in several ways deepening upon the tools user know, and also it depends on the data structure. For an example, a user may want to compare or match two columns and get the result as TRUE or FALSE; some user wants the result in their own words, some users want to highlight all the matching data, some users want to highlight only unique values. Like this, depending on the requirement of the user, we can do the matching.

Match Two Columns in Excel

Examples

Below are the examples of matching or comparing two columns in excel.

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

Example #1 – Compare Two Columns Data

For example, assume you have received city names from two different sources, which are sorted from A to Z. Below is the data set for this example.

Match Columns Example 1

Step 1: We have city names from two different sources; we need to match whether Source 1 data is equal to Source 2 or not. This can be done by simple basic excel formulas. Open Equal sign in C2 cell.

Match Columns Example 1-1

Step 2: Since we are matching Source 1 = Source 2, let’s select the formula as A2 = B2.

Match Columns Example 1-2

Step 3: Press the Enter key. If Source 1 is equal to Source 2, we will get the result as TRUE or else FALSE.

Example 1-3

Step 4: Drag the formula to the remaining cells to get the result.

Example 1-4

Example 1-5

In some cells, we got the result as FALSE (colored cells), which means Source 1 data is not equal to Source 2. Let’s look at each cell in detail.

  • Cell C3: In A3 cell, we have “New York,” and in B3 cell, we have “NewYork.” Here the difference is we don’t have space characters after the word “New.” So the result is FALSE.
  • Cell C7: In A7 cell, we have “Bangalor,” and in cell B7, we have “Bengaluru.” So both are different, and obviously, the result is FALSE.
  • Cell C9: This is a special case. In cell A9 & B9, we have the same value as “New Delhi,” but still, we got the result as FALSE. This is an extreme case but a real-time example. By just looking at the data, we cannot really tell what the difference is; we need to go minute analysis mode.

Step 5: Let’s apply the LEN function in excel for each cell, which tells the number of characters in the selected cell.

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

Example 1-9

Step 6: In cell A9, we have 9 characters, but in cell B9, we have ten characters, i.e., one extra character in cell B9. Press the F2 key (edit) in cell B9.

Example 1-10

Step 7: As we can see, there is one trailing space character entered after the word “Delhi,” which is contributing as an extra character. To overcome these kinds of scenarios, we can apply the formula with the TRIM function, which removes all the unwanted space characters. Below is the way of applying the TRIM function.

Example 1-11

Now, look at the result in cell C9; this time, we got the result as TRUE because since we have applied a TRIM function, it has eliminated the trailing space in cell B9, now this is equal to the cell A9.

Example #2 – Case Sensitive Match

In case if you want to match or compare two columns with case sensitive approach, then we need to use the Exact function in excel.

The Exact Function looks for two values and returns TRUE if the value 1 is equal to value 2. For example, if the value 1 is “Mumbai” and value 2 is “MUMBAI” it will return FALSE because the value 1 characters are in proper format and value 2 characters are in uppercase format.

Take a look at the below data now.

Example 2-1.png

We have two values in the form of fruit names. Now we need to match whether Value 1 is equal to Value 2 or not.

Below is the EXACT formula.

Example 2-2.png

Here, value 1 is equal to value 2, so it returns “True.”

Example 2-3

Drag the formula to other cells.

Example 2-4

We have four values that are not exact.

Example 2-4

  • Cell C3: In cell A3, we have “Orange,” and in cell B3, we have “ORANGE.” Technically both are the same since we have applied case sensitive match function EXACT. It has returned FALSE.
  • Cell C7: In this case, also both the values are different in case matching. Kiwi & KIWI.
  • Cell C8: In this example, only one character is case sensitive. “Mush Milan” and “Mush Milan.”
  • Cell C9: Here, too, we have only one character case sensitive. “Jack fruit” & “Jack Fruit.”

Example #3 – Change Default Result TRUE or FALSE with IF Condition

In the above example, we have got TRUE for matching cells and FALSE for non-matching cells. We can also change the result by applying the IF condition in excel.

If the values match, then we should get “Matching,” or else we should get “Not Matching” as the answer by replacing default results of TRUE or FALSE, respectively.

Let us open the IF condition in cell C3.

Match Columns Example 3

Enter the logical test as A2 = B2.

Example 3-1

If the provided logical test in excel is TRUE, then the result should be “Matching.”

Compare Columns in Excel Example 3-2

If the test is FALSE, then we need the result as “Not Matching.”

Compare Columns in Excel Example 3-3

Hit enter and copy-paste the formula to all the cells to get the result in all the columns.

Compare Columns in Excel Example 3-4

So wherever data is matching, we got the result as “Matching,” or else we got the result as “Not Matching.”

Example #4 – Highlight Matching Data

With the help of conditional formatting, we can highlight all the matching data in excel.

Select the data first and go to conditional formatting. Under Conditional Formatting, select New Rule.

Match Column Example 4

Select Use a formula to determine which cells to format. In the formula, the bar enters the formula as =$A2=$B2.

Compare Columns in Excel Example 3-6

In the Format, the option chooses formatting color.

Compare Columns in Excel Example 3-7

Click on OK. It will highlight all the matching data.

Compare Columns in Excel Example 3-8

Like this, we can match two columns of data in excel in different ways.

Recommended Articles

This has been a guide to Compare Columns in Excel. Here we learn to match and compare two columns using different formulas in excel along with some examples and a downloadable excel template. You may learn more about excel from the following articles –

  • Excel Compare Two Columns Using Vlookup
  • Conditional Formatting | Highlight Dates
  • Convert Column to Number in Excel
  • Column Lock in Excel
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 Match Two Columns Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More