How to Compare Text in Two Columns in Excel

Comparing text in two columns in Excel is a significant task in our daily lives, especially when we have to find something that compares to the given text. In this article, I’ll focus on the seven fruitful ways to compare text in two columns in Excel, with relevant examples.

Compare Text in Two Columns in Excel: 7 Handy Ways

This article will demonstrate to you how to compare text in two columns in Excel by using the arithmetic formula, combining the IF and COUNTIF functions, conditional formatting,  the VLOOKUP function, nesting the INDEX and MATCH functions, and combining SUMPRODUCT, ISNUMBER, and MATCH functions.

Let’s look at the following dataset. Here, two lists of items, namely Item List 1 and Item List 2, are given along with their sales in January and February, respectively.

Right now, we have to compare the item list from different perspectives. Let’s get started.

1. Comparing Text in Two Columns For Matches in Rows

Here, we will demonstrate to you how to compare text in two columns with the three categories such as identical (exact) matching by using the general arithmetic formula, identical matches and differences using the IF Function, and comparing matches or differences with case-sensitive analysis.

1.1  Identical (Exactly) Matching by Using General Arithmetic Formula

Steps:

• Here, B5 is the cell of an item from Item List 1 and C5 is the cell of an item from Item List 2.
• Firstly, select the D5 cell.
• In general, you can use the following formula to compare two columns row by row for identical matching.

• Then, press ENTER.

• So, you will see here the first identical matching in the D5 cell.
• Besides, use the Fill Handle tool and drag it down from the D5 cell to the D16 cell.

• Finally, you can see all the identical matching as true and false.

1.2 Identical Matches and Differences Using IF Function

You can easily find the output regarding matching and not matching (differences) when using the IF formula together. The IF function is a logical function based on a given statement.

The Syntax of the IF Function 

The Arguments of the IF Function

• logical_test – A value or logical expression that can be evaluated as TRUE or FALSE.
• value_if_true – [optional] The value to return when logical_test evaluates to TRUE.
• value_if_false – [optional] The value to return when logical_test evaluates to FALSE.

Steps:

• Here, choose the D5 cell first.
• Now, let’s apply the formula in the case of our dataset.

• After that, hit ENTER.

• Then, you will get the result as NOT Match in the D5 cell.
• Besides, use the Fill Handle tool and drag it down from the D5 cell to the D16 cell.

• Here, you will get all the results.

1.3 Comparing Matches or Differences with Case-sensitive Analysis

In the previous case, we didn’t consider the sensitivity of the text. If you want to compare the item list based on case sensitivity using the EXACT function, you may proceed with the following formula. The EXACT function compares two texts, considering the upper and lower case.

Steps:

• In this image, we will color the given two rows to see the difference.
• Here, choose the D5 cell first.
• Then, let’s apply the formula in the case of our dataset.

• After that, hit ENTER.

• So, you will see here the result in the D5 cell.
• Besides, use the Fill Handle tool and drag it down from the D5 cell to the D16 cell.

• Consequently, In the screenshot, we can see that only the change in F of the Chest Freezer provides the result “Not Match”

Read More: Excel formula to compare two columns and return a value

2. Compare Text in Two Columns by Combining IF and COUNTIF Functions in Excel

In the previous examples, we did comparison row by row, but sometimes we need to work along whole items, not just row by row. In this situation, you may utilize the COUNTIF function.

The COUNTIF function is an Excel function for counting cells within a range that fulfills a specific condition. This function can count cells containing dates, numbers, and text.

 The Syntax of the COUNTIF Function

 The Argument of the COUNTIF Function

range – The range of cells to count.

criteria – The criteria that control which cells should be counted.

Steps:

• Here, choose the D5 cell first.
• Then, let’s apply the formula below here.

• Here, C5:C13 is the cell range for item list 2, and B5 is the cell of an item from item list 1. If the IF function returns zero (Not Found in List 2) or 1 (Found in List 2).
• Then, press ENTER.

• So, you will see here the result in the D5 cell.
• Besides, use the Fill Handle tool and drag it down from the D5 cell to the D16 cell.

• Lastly, you will get all the results here in the below image.

3. Applying Conditional Formatting to Compare Text in Two Columns for Matches and Differences

Using conditional formatting in Excel, you can apply customized formatting to cells that satisfy certain criteria with highlighting colors.

Let’s apply the feature to compare the two lists of items.

3.1 Finding Matches

You can find the matched item if you follow the below steps.

Steps:

• Firstly, go to Home>Conditional Formatting>New Rule.

• Then, select Use a formula to determine which cells to format option and insert the formula in the blank space as in the following screenshot.

• Later, click on Format.

• After that, go to the Fill option, choose your desired color, and press OK.

• Again press OK in the New Formatting Rule dialog box.

• As a result, you’ll get the following output. Only the speaker and desktop monitor are matched.

3.2 Finding Differences

Steps:

• Here, to find the differences, you have to do the same procedure as the previous way except insert the following formula instead of the earlier one.

• Finally, you will get the following output.

4. Highlighting Duplicate or Unique Text to Compare in Two Columns Using Conditional Formatting

In this method, we’ll use Conditional Formatting again except for the formula and utilize the Highlight Cells Rules option of the feature.

4.1 Finding Duplicate Text (Matched Text)

You can identify duplicate items without any formula. For this, follow the below steps.

Steps:

• Here, select Home>Conditional Formatting>Highlight Cells Rules>Duplicate Values.

• Then open the Duplicate Values.
• Later, preserve the default Duplicate option in the Format cells that contain it, change the values with the option (simply it shows the color), and press OK.

• You’ll get the following output.

4.2 Finding Unique Text (Not Matched Text)

Also, you can identify the unique names of the items where duplicate texts are available.

Steps:

• So, follow the previous steps till the dialog box namely Duplicate Values. In the dialog box, change the default option to Unique and press OK.

• After following the above steps, you’ll get the following output.

5. Using VLOOKUP Function For Comparing and Finding Missing Text in Excel

Well, you may have to find the missing text from two given columns of text. Like if you wish to determine if an item on one list is in the other list or not, you may utilize the VLOOKUP function. VLOOKUP is an Excel function for vertically organized data searches in a table. The function is compatible with both approximate and exact matching.

The Syntax of the VLOOKUP Function 

The Argument of the VLOOKUP Function 

• value – The value to look for in the first column of a table.
• table – The table from which to retrieve a value.
• col_index – The column in the table from which to retrieve a value.
• range_lookup – [optional] TRUE = approximate match (default). FALSE = exact match.

Steps:

• Firstly, choose the D5 cell.
• Then, the formula will be like the following for our dataset.

• After that, press ENTER.

• So, you will see here the first identical matching in the D5 cell.
• Besides, use the Fill Handle tool and drag it down from the D5 cell to the D16 cell.

• Finally, you can see all the identical matching as true and false.

6. Comparing Text and Extracting Data by Nesting INDEX and MATCH Functions

If you guys need to extract or return the value of the matched items, you can utilize the combination of the INDEX MATCH function. The INDEX function in Excel returns the value that is located at a specified place in a range or array.

The Syntax of the INDEX Function 

The Argument of the INDEX Function 

• array – A range of cells, or an array constant.
• row_num – The row position in the reference or array.
• col_num – [optional] The column position in the reference or array.
• area_num – [optional] The range in reference that should be used.

The MATCH function is used for locating the search value location in a row, column, or table. The MATCH is often coupled with the INDEX function to retrieve a corresponding value.

The Syntax of the MATCH Function 

The Argument of the MATCH Function 

• lookup_value – The value to match in lookup_array.
• lookup_array – A range of cells or an array reference.

Steps:

• Suppose, you have given a list of lookup items that are available in another list of items with their sales. Now, you have to extract the sales for the matched items.
• For that, you have to use the following formula.

• Then, press ENTER.
• Here, B5:C16 is the list of items with their sales, E5 is a lookup item, B5:B16 is the item list, 0 is for the exact matching, and 2 is for the column index.

• So, you will see here the Sales value in the D5 cell.
• Besides, use the Fill Handle tool and drag it down from the D5 cell to the D16 cell.

• Lastly, you will get all the sales value here in the below image.

7. Merging SUMPRODUCT, ISNUMBER, and MATCH Functions to Compare Text in Two Columns with Counting Matches

If you want to count the number of matched text or items, you can utilize the SUMPRODUCT function. The formula is an extraordinarily multifaceted, but rather flexible function that is suitable for summing such as SUMIFS.

The Syntax of the SUMPRODUCT Function 

The Argument of the SUMPRODUCT Function 

• array1 – The first array or range to multiply, then add.
• array2 – [optional] The second array or range to multiply, then add.

Steps:

• Firstly, select the D5 cell.
• Secondly, write down the following formula n the case of our dataset.

• Then, hit ENTER.
• In this formula, B5:B16 is the cell range for item list 1, and C5:C13 is for item list 2. Besides, the –ISNUMBER function is used to transform the output into numerical values.

• Finally, you will see the following output in the given image.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it yourself.

Conclusion

In this article, we’ve covered 7 handy methods to compare text in two columns in Excel. We sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below. Goodbye!

<< Go Back to Columns | Compare | Learn Excel