Match and Compare Two Columns in Excel (Table of Contents)
- Compare Two Columns Data
- Case Sensitive Match
- Change Default Result TRUE or FALSE with IF Condition
- Highlight Matching Data
How to Compare and Match Columns in Excel?
When we have the same set of data in parallel columns often times we try to match both the columns and see whether the parallel cells data is the same or not. In most of the excel forums, we can see this question raised by quite a few people those who use excel in their workplace. Comparing and matching two columns in excel data can be done in several ways deepening up on 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 upon the requirement of the user we can do the matching.
In this article, we will show you how to compare or match two columns in excel.
How to Compare Columns in Excel?
Below are the examples of matching or comparing 2 columns in excel.
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.
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 a simple excel formula. Open Equal sign in C2 cell.
Since we are matching Source 1 = Source 2, let’s select the formula as A2 = B2.
Press Enter key. If the Source 1 is equal to Source 2 we will get the result as TRUE or else FALSE.
Drag the formula to the remaining cells to get the result.
In some cells, we got the result as FALSE (colored cells) that 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 character after the word “New”. So the result is “FALSE”.
- Cell C7: In A7 cell we have “Bangalore” and in cell B7 we have “Bengaluru”. So both are different and obviously result is FALSE.
- Cell C9: This is the special case. In cell A9 & B9 we have the same value of “New Delhi” but still we got the result as “FALSE”. This is the extreme case but real time example. By just looking at the data we cannot really tell what the difference is, we need to go minute analysis mode.
Let’s apply LEN function for each cell which tells the number of characters in the selected cell.
In cell A9 we have 9 characters but in cell B9 we have 10 characters i.e. one extra character in cell B9. Press F2 key (edit) in cell B9.
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 TRIM function which removes all the unwanted space characters.
Below is the way of applying the TRIM function.
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 2 columns with case sensitive approach then we need to use the Exact function of excel.
The Exact Function looks for two values and returns TRUE if the value 1 is equal to value 2. For an example if the value 1 is “Mumbai” and value 2 is “MUMBAI” it will return FALSE because value 1 characters are in proper format and value 2 characters are in uppercase format.
Take a look at the below data now.
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.
Here, value 1 is equal to value 2 so it returns “True”.
Drag the formula to other cells.
We have four values that are not exact.
- Cell C3: In cell A3 we have “Orange” and in cell B3 we have “ORANGE”. Technically both are 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 IF condition in cell C3.
Enter the logical test as A2 = B2.
If the provided logical test is TRUE then the result should be “Matching”.
If the test is FALSE then we need the result as “Not Matching”.
Hit enter and copy paste the formula to all the cells to get the result in all the columns.
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”.
Choose “Use a formula to determine which cells to format”. In the formula, bar enters the formula as =$A2=$B2.
In the Format, option chooses formatting color.
Click on OK. It will highlight all the matching data.
Like this, we can match 2 columns of data in excel in different ways.
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 downloadable excel template. You may learn more about excel from the following articles –