Excel Compare Two Columns for Matches
Excel is a popular tool to store data and we all know that. Storing the data isn’t the last job we do in excel, we tell the story behind the data as well with various analysis and techniques. Before we tell the summary we need to do a lot of work as well, so reconciliation is one of the workings we need to do. Comparing two columns data is the common task we all do and in this article, we will show you how to compare two columns in excel for the matches.
How to Compare Two Columns in Excel for the Matches?
Below are the techniques to compare two columns in excel for the matches with examples.
Example #1
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.
For example, look at the below data in two columns.
Now we need to match the above two-column data, first, copy the above table to the worksheet in excel.
The first method we can use to match this two-column is to put an equal sign in cell C2 and choose A2 cell.
Since we are testing whether column 1 value is equal to column 2 put the equal sign and choose B2 cell.
This formula checks whether 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 enter key and apply the excel formula to other cells also to get the matching result in all the cells.
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 two 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.
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.
On cell A4 we have 6 characters and in cell B4 we have 7 characters this is because there may be extra leading space character, so to deal with these scenarios we can use TRIM function while applying matching formula. Enclose cell references in the TRIM function.
Now look at the result in C4 cell it is showing TRUE as the result, thanks to 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.
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 are 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.
For Text 1 argument choose A2 cell.
For Text 2 argument choose B2 cell.
Close the bracket and hit enter key to get the result.
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 the matches using TRIM and EXACT function along with examples and downloadable excel template. You may also look at these useful functions in excel –
- Use Convert Function in Excel
- Excel Statistics Function
- Excel Match Multiple Criteria
- Index Match Multiple Criteria Rows and Columns
- How to Compare and Match Columns in Excel?
- Match Function in Excel
- Match Data in Excel
- Compare Two Excel Columns
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
