How to Compare Two Columns in Excel for the Matches?

We have several methods in Excel to compare two columns for the matches. Of course, it depends on data structure and Excel skills too. But, let us try some of them in this article to understand the concept of comparing two columns.

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 we can use to match these two columns is to put an equal sign in cell C2 and choose the A2 cell.

  • Since we are testing whether column 1 value is equal to 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, it will show the result as “TRUE” or “FALSE.”

  • Press the “Enter” key and apply the excel formulaApply The Excel FormulaThe term “basic excel formula” refers to the general functions used in Microsoft Excel to do simple calculations such as addition, average, and comparison. SUM, COUNT, COUNTA, COUNTBLANK, AVERAGE, MIN Excel, MAX Excel, LEN Excel, TRIM Excel, IF Excel are the top ten excel formulas and functions.read more 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 B2 cell, we have a value of “Colambo” since both these values are different, we got the matching result is “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 in these cases.

First, we will apply the LEN functionApply The LEN FunctionThe Len function returns the length of a given string. It calculates the number of characters in a given string as input. It is a text function in Excel as well as an inbuilt function that can be accessed by typing =LEN( and entering a string as input.read more 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 functionUse The TRIM FunctionThe Trim function in Excel does exactly what its name implies: it trims some part of any string. The function of this formula is to remove any space in a given string. It does not remove a single space between two words, but it does remove any other unwanted spaces.read more while applying a matching formula to deal with these scenarios. Enclose cell referencesCell ReferencesCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more 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 a case sensitive match.

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

The first one is “FALSE” because both the cell values are different. 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, so the EXACT function looks for exact values and gives the result as “FALSE” if there is any deviation in character cases.

  • We have modified the above data as below. Then, we need to open the EXACT function. For Text 1 argument, choose the A2 cell. For the Text 2 argument, choose the B2 cell. Then, close the bracket and press the Enter key to get the result.

Things to Remember

  • We can easily match the two cells by inserting the operating symbol equal (=) sign in between two cells. We can match only two columns at the same time.EXACT function matches two cell values and considers both the cells’ case sensitive characters.

This article has been a guide to Compare Two Columns in Excel for Matches. Here, we discuss comparing two columns in Excel for exact matches using TRIM and EXACT function, along with examples and a downloadable Excel template. You may also look at these useful functions in Excel: –

  • Compare Two Columns Using VlookupHow to Compare Two Columns in Excel?Excel Match Multiple CriteriaIndex Match Multiple Criteria Rows and Columns