Compare Two Columns In Excel For Match Step By Step Examples

Compare Two Columns in Excel for Match

Comparing and matching two columns in Excel data can be performed in several ways depending upon the tools a user knows. It also depends on the data structure. For example, a user may want to compare or match two columns and get the result as TRUE or FALSE. Some users require the result in their own words. At the same time, some users want to highlight all the matching data. Some users want to highlight unique values. Like this, we can do the matching depending on the user’s requirement.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Compare and Match Columns in Excel (wallstreetmojo.com)

Examples

Below are examples of matching or comparing two columns in excelComparing Two Columns In ExcelTwo columns in excel are compared when their entries are studied for similarities and differences.read more.

Example #1 – Compare Two Columns of Data

For example, assume we have received city names from two different sources, sorted from A to Z. Below is the data set.

Example #2 – Case Sensitive Match

If we want to match or compare two columns with a case sensitive approach, we need to use the Exact function in excelExact Function In ExcelThe exact function is a logical function in excel used to compare two strings or data with each other, and it gives us the result whether the both data are an exact match or not. This function is a logical function, so it provides true or false as a result.read more.

Now, let us look at the result in cell C9. We got the result as “TRUE” because we have applied a TRIM function. It has eliminated the trailing space in cell B9. Now, this is equal to cell A9.

The EXACT function looks for two values and returns “TRUE” if the value 1 is equal to value 2. For example, if the value 1 is “Mumbai” and value 2 is “MUMBAI,” it will return “FALSE” because the 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. Therefore, we need to match whether “Value 1” equals “Value 2.”

Below is the EXACT formula.

Here, “Value 1” equals “Value 2,” so it returns “True.”

Then, 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 the same since we have applied case sensitive match functionMatch FunctionThe MATCH function looks for a specific value and returns its relative position in a given range of cells. The output is the first position found for the given value. Being a lookup and reference function, it works for both an exact and approximate match. For example, if the range A11:A15 consists of the numbers 2, 9, 8, 14, 32, the formula “MATCH(8,A11:A15,0)” returns 3. This is because the number 8 is at the third position.
read more in Excel. However, it has returned “FALSE.”Cell C7: Both the values are different in case matching: “Kiwi” and “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” and “Jack Fruit.”

Example #3 – Change Default Result TRUE or FALSE with IF Condition

We have “TRUE” for matching cells and “FALSE” for non-matching cells in the above example. We can also change the result by applying the IF condition in excelApplying The IF Condition In ExcelIF function in Excel evaluates whether a given condition is met and returns a value depending on whether the result is “true” or “false”. It is a conditional function of Excel, which returns the result based on the fulfillment or non-fulfillment of the given criteria. read more.

If the values match, we should get “Matching,” or we should call “Not Matching” as the answer by replacing default results of “TRUE” or “FALSE,” respectively.

Let us open the IF condition in cell C3.

And enter the logical test as A2 = B2.

If the provided logical test in excelLogical Test In ExcelA logical test in Excel results in an analytical output, either true or false. The equals to operator, “=,” is the most commonly used logical test.read more is “TRUE”, the result should be “Matching.”

If the test is “FALSE,” we need the result as “Not Matching.”

Press the “Enter” key and copy-paste the formula to all the cells to obtain the result in all the columns.

So wherever data is matching, we get the result as “Matching,” or else we get the result as “Not Matching.”

Example #4 – Highlight Matching Data

With the help of conditional formattingConditional FormattingConditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions. It can be found in the styles section of the Home tab.read more, we can highlight all the matching data in excel.

We must select the data first and go to conditional formatting. Under conditional formatting, we must choose “New Rule.”

Then, select “Use a formula to determine which cells to format.” In the formula, the bar enters the formula as =$A2=$B2.

In the Format, the option chooses formatting color.

Click on “OK.” It will highlight all the matching data.

Like this, we can match two columns of data in Excel in different ways.