Excel Compare Text in Two Columns (7 Fruitful Ways)

Comparing text in two columns in Excel is a significant task in our daily life especially when you have to find something compared to the given text.

In this article, I’ll focus on the seven fruitful ways to compare text in two columns with relevant examples.

Download Practice Workbook

 

How to Enter a Formula in Excel

Do you know how can we insert a formula in Excel?

Entering a formula in the Excel formula bar is quite a simple task.

First, you have to select a blank cell where you want to show the output. Then input an equal sign (=). And then insert the formula with proper parenthesis. Finally, press Enter.

Furthermore, you can utilize the Fill Handle Tool for using the same formula for the other cell values. More importantly, you have to use $ (Dollar sign) before a cell for using the cell as a reference cell.

Excel Compare Text in Two Columns

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

Dataset for comparing text in two columns

Right now, we have to compare the items list from different perspectives.

Let’s get started.

1. Comparing Text in Two Columns Row by Row

i. Identical (Exactly) Matching in A Simple Way

In general, you can use the following formula to compare two columns row by row for identical matching.

=B5=C5

Here, B5 is the cell of an item from Item list 1 and C5 is the cell of an item from item list 2.

Identical (Exactly) Matching in A Simple Way

ii. Identical Matching and Differences Using IF Formula

You can easily find the output regarding matching and not matching (differences) combinedly using the IF formula.

IF function is a logical function based on a given statement. The syntax of the function is

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

The arguments of the syntax are the following

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.

Now, let’s apply the formula in the case of our dataset and it will be-

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

Here, B5 is the cell of an item from Item list 1 and C5 is the cell of an item from item list 2.

Identical Matching and Differences Using IF Formula

iii. Matching or Differences with Case-Sensitive Formula

In the previous case, we didn’t consider the sensitivity of the text. Like if you want to compare the item list based on case-sensitive using the EXACT function, you may proceed with the following formula.

=IF(EXACT(B12,C12),"Match","Not Match")

Here, B12 is the cell of an item from Item list 1 and C12 is the cell of an item from item list 2.

Matching or Differences with Case-Sensitive Formula

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

Note. The EXACT function compares two texts considering upper and lower case.

2. Comparing Text in Two Columns for Matches & Differences

In the previous examples, we did comparison row by row but sometimes we need to work along whole items not limited to row by row.

In this situation, you may utilize the COUNTIF function.

The COUNTIF 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 function is

=COUNTIF (range, criteria)

range – The range of cells to count.

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

=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, 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).

Comparing Text in Two Columns for Matches & Differences

3. Comparing Text in Two Columns for Matches & Differences Using Conditional Formatting

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

Let’s apply the feature in comparing the two lists of items.

i. Finding Matches

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

Go to Home>Conditional Formatting>New Rule

Finding Matches Using Conditional Formatting

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

=$B5=$C5

Later, click on Format

Finding Matches Using Conditional Formatting

Go to the Fill option, choose your desired color, and press Ok.

Again press Ok in the New Formatting Rule dialog box.

Finding Matches Using Conditional Formatting

You’ll get the following output. Only the speaker and desktop monitor are matched.

Finding Matches Using Conditional Formatting

ii. Finding Differences

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

Finding Differences Using Conditional Formatting

4. Comparing & Highlighting Duplicate or Unique Text in Two Columns  Using Conditional Formatting

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

i. Finding Duplicate Text (Matched Text)

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

Select Home>Conditional Formatting>Highlight Cells Rules>Duplicate Values

Comparing & Highlighting Duplicate Text

Then open the Duplicate Values.

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

Comparing & Highlighting Duplicate Text

You’ll get the following output.

Comparing & Highlighting Duplicate Text

ii. Finding Unique Text (Not Matched Text)

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

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

Comparing & Highlighting Unique Text

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

Comparing & Highlighting Unique Text

5. Comparing & Finding Missing Text Using VLOOKUP Formula

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 function is

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

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.

The formula will be like the following for our dataset.

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

Here, B5 is the lookup item, C5:C13 is the cell range for item list 2,

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 the error. If the result is an error, the function will return as TRUE, and FALSE if the result is not an error.

Comparing & Finding Missing Text Using VLOOKUP Formula

6. Comparing Text & Extracting Data Using INDEX MATCH Formula

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 function is

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

The arguments are-

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 function is

=MATCH (lookup_value, lookup_array, [match_type])

The arguments are-

lookup_value – The value to match in lookup_array.

lookup_array – A range of cells or an array reference.

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)

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, 2 is for the column index.

Comparing Text & Extracting Data Using INDEX MATCH Formula

7. Comparing Text in Two Columns and Count Matches Using SUMPRODUCT Formula

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

The syntax of the function is

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

There are the following arguments in the function.

array1 – The first array or range to multiply, then add.

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

In the case of our dataset, the formula will be-

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

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

Comparing Text in Two Columns and Count Matches Using SUMPRODUCT Formula

Conclusion

This is how you can compare text in two columns in excel. If you have any confusion or suggestions, please let me know in the following comments section.

Thanks for being with me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo