# How to Find Similar Text in Two Columns in Excel (6 Ways)

We often need to find similar texts in two columns in Excel. Well, there are quite a few handy techniques to do that. I am going to discuss 5 different methods to identify similar texts or characters in two columns in Excel.

**Quickest Way to Find Similar Text in Two Columns in Excel**

**To find similar texts in two columns,**

🔗 **How to Find Duplicates in Two Columns in Excel (7 Methods)**

**Alternative Way ****#1. Use the IFERROR & SEARCH Function to Find Similar Text in Two Columns in Excel**

Here is an example of a dataset regarding building facilities. I need to find if the texts of **Column A** are available in **Column B** or not.

To find the matching texts inside the two columns, I used the **SEARCH function** and **IFERROR function**.

The syntax here is,

**=IFERROR(IF(SEARCH(find_text,within text)[true_value],[false_value])**

Only the **SEARCH function** could do the work of finding the same texts. But that will show **#VALUE!** when matches won’t be found in the two adjacent cells.

So to get rid of this problem, I applied the **IFERROR function **here. This will show the **True_values** as **Present**, and **False_values** as **Absent**.

**Usage Guide**

- First, insert a new column adjacent to the main data table to show results.

I have created the new column in **Column C** and named it ‘**Similar Text**’.

- Then write the following formula in its first cell (
**C2**):

** =IFERROR(IF(SEARCH(A2,B2),"Present"),"Absent")**

- Now press
**ENTER**. - Then put the cursor over the
**Fill Handle**, the tiny ‘**+**’ sign at the right-bottom corner of the**C2**cell, and drag it down from**C2-C10.**

The presence of similar texts resulted in the **Present**, and the dissimilarities are termed **Absent**.

**🔗 How to Compare Rows in Excel for Duplicates (7 Ways)**

**Alternative Way #2. Use IF, MID & SEARCH Functions to Find Similar Text in Two Columns in Excel**

If you are looking for a way that will pull out the exact same texts lying inside the adjacent columns, you can use this method.

For this occasion, I am using the previously shown example datasheet.

The** IF function** will check the presence and absence of similar texts.

The syntax is,

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

As I have 3 columns, I will be using a **MID function** that will detect the matched words from the left column with the mid one, in terms of the right column.

Here, the **MID function** syntax is,

**MID(text,start_number,number_characters)**

Here,

**Text:**is the**text string**that has the texts you want to extract**Start_number:**is the array of the first text.**Number_characters:**defines the number of characters you want to draw out from the middle column.

**Usage Guide**

- First, create a new column next to the previous columns (
**Column D**). - Then apply this formula in its top cell (
**D2**):

**=IF(C2="Present",MID(B2,SEARCH(A2,B2),LEN(A2))," ")**

- Now press
**ENTER**. - After that, put the cursor over the
**Fill Handle**icon and drag it down from**D2**to**D10**.

The exactly matched text strings will appear on the cells where they are available, and unmatched results will show as **empty strings**.

**🔗 5 Ways to Find Matching Values in Two Worksheets in Excel**

**Alternative Way #****3. Use the FIND Command to Find Similar Text in Columns in Excel**

It is a quick technique that can find out your demanded texts or characters from the entire datasheet. I am using a simple dataset as an example here. But no doubt, your worksheet will be way more complex in reality.

**Usage Guide**

- First, go to the
**Home**tab. - Then, under the
**Editing**group, click on the**Find & Select**drop-down list. - Then select
**Find**.

The **Find and Replace **dialog box will show up.

- Write the text that you are looking for inside the
**Find What**box. - Then press the
**Find All**button.

I searched for the word **‘Kiwi’** and all the text strings containing **‘kiwi’** come along in the extended part of the dialogue box.

**🔗 5+ Formulas to Find Duplicates in One Column in Excel**

**🔗 Find, Highlight, and Remove Duplicates in Excel [Step-by-Step]**

**Alternative Way #****4. Use the VLOOKUP Function to Find Texts of Two Adjoining Columns in a Separate Column in Excel **

This method will help you find similar texts in two distinct columns and place their adjoining values in another data column with its previous arrangement.

**Usage Guide**

- First, write the following formula in the
**E4**:

**=VLOOKUP(E4,$A$2:$B$13,2,0)**

**Formula Breakdown**

**E4:**is the lookup value.**$A$2:$B$13:**is the table array.**2:**is the**column index number**, which depends on the column headers.**0:**is the**range lookup**.

- Then press
**ENTER.** - Then put the cursor over the
**Fill Handle**and drag it down from**E4**to**E8**.

The formula is now applied to the entire column.

**🔗 4+ Methods to Filter Duplicate Values in Excel**

**🔗 How to Remove Duplicates in Excel [13 + Different Methods]**

**Alternative Way #****5. Use TRUE & FALSE Logical Formula to Find Similar Text in Two Columns in Excel**

I am using a list of fruits from which I will be uncovering the same fruit names using the **TRUE-FALSE** logical formula.

- First, create a new column (
**Column C**).

I named it **Results**.

- Then, insert the following formula in its first cell (
**C2**):

**=A2=B2**

Here,

**A2:**is the first input of**column A**.**B2:**is the first input of**column B**.

The** ‘****=****’ **sign before the functions compares the two columns and finds out if they are equal or not.

- After that, press
**ENTER**.

If the cells got similar texts, the result is **TRUE**. If the cells mismatch, the result is **FALSE**.

- Then drag down the
**Fill Handle**over the rest of the cells (**C2:C13**)**.**

The outcome looks something like this:

**🔗 4+ Ways to Find Duplicates in a Column and Delete Rows in Excel**

**🔗 Vlookup for Duplicate Values and Return the Matches in Excel [8 Cases]**

**Conclusion**

Microsoft Excel offers several other ways to easily find similar words in your worksheet. But I tried to cover the most useful ones in this article. If you have any suggestions and more techniques that I can add to this blog, do share them with me in the comment section.

Hi iam trying to apply this formula to compare the text in 2 columns using below formula but it was not showing anything,what i did wrong?

=IFERROR(IF(SEARCH(A2,B2),”Present”),”Absent”)

Hi Madhuri,

I think you have directly copied the formula from this blog to your Excel sheet. If the case is so, I suggest you erase all the double inverted commas (“”) from the formula and manually insert them again using your own keyboard. I think that will resolve the formula syntax issue.

Regards!