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.

**Table of Contents**Expand

## 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

**functions, conditional formatting, the**

*COUNTIF***function, nesting the**

*VLOOKUP***INDEX**and

**MATCH**functions, and combining

*SUMPRODUCT,***and**

*ISNUMBER,***functions.**

*MATCH*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,
is the cell of an item from*B5***Item List 1**andis the cell of an item from*C5***Item List 2**. - Firstly, select the
cell.*D5* - In general, you can use the following formula to compare two columns row by row for identical matching.

`=B5=C5`

- Then, press
**ENTER.**

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

- 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 **

`=IF(logical_test, [value_if_true], [value_if_false])`

**The Arguments of the IF Function**

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

**Steps:**

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

`=IF(B5=C5,"Match","Not Match")`

- After that, hit
**ENTER**.

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

- 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
cell first.*D5* - Then, let’s apply the formula in the case of our dataset.

`=IF(EXACT(B5,C5),"Match","Not Match")`

- After that, hit
**ENTER**.

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

- 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**

`=COUNTIF(range, criteria)`

** 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
cell first.*D5* - Then, let’s apply the formula below here.

`=IF(COUNTIF($C5:$C13, $B5)=0, "Not Found in List 2", "Found in List 2")`

- Here,
**C5:C13**is the cell range for item list 2, andis 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).*B5* - Then, press
**ENTER**.

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

- 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.

`=$B5=$C5`

- 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.

`=$B5<>$C5`

- 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 **

`=VLOOKUP(value, table, col_index, [range_lookup])`

**The Argument of the VLOOKUP Function **

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

**Steps:**

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

`=ISERROR(VLOOKUP(B5,$C$5:$C$13,1,0))`

- After that, press
**ENTER**.

**Formula Breakdown**

- Here,
is the lookup item,*B5*is the cell range for item list 2,*C5:C13* - You have to find whether the
(*B5***AC**) is found in item list 2 or not. - Now, if the lookup item (
**AC**) is found in item list 2, the**VLOOKUP**formula returns the name of the item. Otherwise, if**AC**is not found in list 2, the formula returns a**#N/A error**. So, this is the missing item. - Furthermore, the
**ISERROR**function is used to avoid errors. If the result is an error, the function will return as**TRUE**, and**FALSE**if the result is not an error.

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

- 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 **

`=INDEX(array, row_num, [col_num], [area_num])`

**The Argument of the INDEX Function **

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

*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 **

`=MATCH(lookup_value, lookup_array, [match_type])`

**The Argument of the MATCH Function **

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

**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.

`=INDEX($B$5:$C$16,MATCH(E5,$B$5:$B$16,0),2)`

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

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

- 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 **

`=SUMPRODUCT(array1, [array2],...)`

**The Argument of the SUMPRODUCT Function **

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

**Steps:**

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

`=SUMPRODUCT(--(ISNUMBER(MATCH(B5:B16,C5:C13,0))))`

- Then, hit
**ENTER**. - In this formula,
is the cell range for item list 1, and*B5:B16*is for item list 2. Besides, the*C5:C13***–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**