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

**Find Similar Text in Two Columns in Excel ****Using IFERROR Function**

To find similar texts in two columns in Excel, follow these steps below:

- Insert a new column (
**Column C**). - Then, write the following formula in its first cell (
**C2**):**=IFERROR(IF(SEARCH(A2,B2),”Present”),”Absent”)** - Now, press
**ENTER**. - Then drag the
**Fill Handle**down till the end of the column (**C2:C10**).

**Find Similar Text in Two Columns in Excel by ****IFERROR & SEARCH Functions**

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

**Syntax**

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

To find the similar text in two columns in Excel using** IFERROR** & **SEARCH** function, follow the steps below:

- Insert a new column (
**Column C**) adjacent to the main data table to show the results. - Write the following formula in its first cell (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 cell**C2**to**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)****How to Find Duplicates in Two Columns in Excel (7 Methods)**

**Find Similar Text in Two Columns in Excel with**** IF, MID & SEARCH Functions**

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.

**Syntax**

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

**Formula Explanation**

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

To use the **IF**, **MID** & **SEARCH** functions to find similar text in two columns, follow this guideline below:

- Create a new column (
**Column D**) next to the dataset. - Then apply this formula in its top cell (
**D2**):**=IF(C2=”Present”,MID(B2,SEARCH(A2,B2),LEN(A2)),” “)** - Now, press
**ENTER**. - Drag the
**Fill Handle**down from cell**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**.

**Find Similar Text in Columns in Excel Using**** Find Command**

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.

Find similar text in columns in Excel, go through these steps below:

- Go to the
**Home**tab. - From the
**Editing**group, click on the**Find & Select**drop-down list. - Select
**Find**. - In the
**Find and Replace**dialog box, write the text that you are looking for inside the**Find What**box. - 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]****5 Ways to Find Matching Values in Two Worksheets in Excel**

**Find Texts of 2 Adjoining Columns in Separate Column in Excel by**** VLOOKUP Function**

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.

**Formula**

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

Use the **VLOOKUP **function to find texts of two adjoining columns:

- Select cell
**E4**. - Write the following formula:
**=VLOOKUP(E4,$A$2:$B$13,2,0)** - Then, press the
**ENTER**key. - Put the cursor over the
**Fill Handle**and drag it down from cell**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]**

**Find Similar Text in Two Columns in Excel by TRUE-FALSE Logical Formula**

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

Find similar text in two columns using the **TRUE** and** FALSE **logical formula using these steps:

- First, create a new column (
**Column C**). - Then, insert the following formula in its first cell (
**C2**):**=A2=B2** - Press the
**ENTER**key. - Then, drag down the
**Fill Handle**over the rest of the cells (**C2:C13**)**.**

If the cells got similar texts, the result is **TRUE**. If the cells mismatch, the result is **FALSE**. 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.

## Frequently Asked Questions

### How do I find two similar cells in Excel?

To find two similar cells in Excel, you can use **Conditional Formatting**. Here’s a concise guide:

- Click on the first cell of the range you want to check for similarity.
- Go to the
**Home**tab in the Excel ribbon. - Click on
**Conditional Formatting**. - Hover over
**Highlight Cells Rules**. - Select
**Duplicate Values**from the submenu. - In the
**Duplicate Values**dialog box, select formatting options. For example, choose a fill color.

Click**OK**to apply the formatting.

Excel will highlight cells with similar content based on your chosen formatting. This method quickly identifies and highlights cells with similar content, making it easy to spot duplicates or similarities in your Excel data. Adjust formatting options as needed for your preference.

### How do I find matching values in two columns in Excel?

To find matching values in two columns in Excel, you can use the VLOOKUP or MATCH functions. Here’s a concise guide:

- Choose a column where you want to display the matching values.
- Type the following formula:
**=IFERROR(VLOOKUP(A1, B:B, 1, FALSE), “”)**

Here**A1**is the cell in the first column you want to check, and**B:B**is the range of the second column. - Drag the formula down to apply it to the entire column.
- The new column will display matching values from the first column that are also present in the second column. Blank cells indicate no match.

Alternatively, you can use the**MATCH**function in a similar way:**=IFERROR(IF(MATCH(A1, B:B, 0), A1, “”), “”)**

This method allows you to quickly identify matching values between two columns in Excel, providing a clear result in a separate column. Adjust the formulas based on your specific column and cell references.

### How do I match partial text in two columns in Excel?

To match partial text in two columns in Excel, you can use the **IF** and **SEARCH** functions. Here’s a concise guide:

- Choose a column where you want to display the matching partial text.
- Type the following formula:
**=IF(ISNUMBER(SEARCH(A1, B1)), A1, “”)**

Here,**A1**is the cell in the first column you want to check, and**B1**is the corresponding cell in the second column. - Drag the formula down to apply it to the entire column.

The new column will display partial text matches from the first column that are found within the corresponding cells in the second column. Blank cells indicate no match.

This method allows you to identify partial text matches between two columns in Excel, providing a clear result in a separate column. Adjust the formulas based on your specific column and cell references.

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!

How to add 2 conditions in this formula? =IF(C2=”Present”,MID(B2,SEARCH(A2,B2),LEN(A2)),” “)

I’ve tried with IFS function but it is not working

showing as “Value” error.

To add two conditions in the formula, you can use the AND function inside the IF statement. The AND function allows you to combine multiple conditions that must all be true for the formula to return a specific result. Here’s how you can modify the formula to include two conditions:

=IF(AND(C2=”Present”, SEARCH(A2, B2) > 0), MID(B2, SEARCH(A2, B2), LEN(A2)), ” “)

Make sure that the cells referenced in the formula (C2, B2, and A2) contain the correct values and data types. If you still encounter a “Value” error, check that the data in the cells matches the expected criteria.