Excel Functions Tutorials
- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
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 –