Compare Two Columns In Excel For Matches

Compare Two Columns in Excel for the Matches means comparing the cell values from columns of a dataset to find matches, and return TRUE or FALSE accordingly.

We have several methods in Excel to compare two columns for the matches. But, of course, it depends on data structure and Excel skills too.

For example, we will apply a simple formula =A1=B1 to check if the cell values match using the “=” equal sign, where A1 and B1 are cells with similar or different values.

If there is a match, we will get the logical value as an output, as TRUE else FALSE.

Here, the output is TRUE, as both the selected cells are empty and a match.

We can Compare Two Columns in Excel for the Matches using 2 methods, namely,

1. Simple Method.
2. Match Case Sensitive Values

We will consider specific examples for the above-mentioned methods.

Example #1 - Simple Method

Look at the below data in two columns. We need to match the below two columns of data. But first, we must copy the above table to the worksheet in Excel.

• The first method to match these two columns is to put an equal sign in cell C2 and choose the A2 cell.

Since we are testing whether the column 1 value equals column 2, we must put the equal sign and choose the B2 cell.

This formula checks whether the A2 cell value equals the B2 cell value. If it matches, the result will be “TRUE” or “FALSE.”

• Press the "Enter" key and apply the excel formula to other cells to get the matching result in all the cells.

• The first result is “FALSE” because, in cell A2, we have a value of “Colomobo,” and in the B2 cell, we have a value of “Colambo”. Since these values are different, we get the matching result as “FALSE.”
• The following result is “TRUE” because, in both, the cell values are “Bangalore,” and the matching result is “TRUE.”
• The next matching result is “FALSE,” even though the cell values look similar. So, we need to dig deep to find the actual variance between these two cell values.

First, we will apply the LEN function to find the number of characters in both cells.

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

Now, look at the result in the C4 cell. It shows “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, i.e., both the cell values should be case-sensitive matches.

We can use Excel’s EXACT function to match two column values based on case sensitivity.

Follow the steps to match case-sensitive values.

1. We have modified the above data as shown below.

   
   

2. Then, we need to open the EXACT function. For the Text 1 argument, choose the A2 cell.

   
   

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

   
   

4. Then, close the bracket and press the Enter key to get the result.

   
   

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

• We can match only two cells from the same or different rows or columns at the same time using the equal sign method.
• More than two cells comparison gives an error when we use the EXACT() function.