How to Find Similar Text in Two Columns in Excel (3 Easy Ways)

Depending on the circumstances you may need to find similar text within two columns in Excel. Today, we are going to show you how to find similar text in two columns in Excel. For conducting the session, we are using Excel 2019. Feel free to use your version. So let’s get started.

Download Practice Workbook

You are welcome to download the workbook from the link below

3 Easy Ways to Find Similar Text in Two Excel Columns

Now, we will discuss 3 effective ways to find similar text in two columns in excel. But before diving into the big picture, let’s get to know about the Excel book which is going to be our base of examples for today.

To keep things simple we have selected a basic table of two columns; Student Name and Summary. We will find the similarity between the elements of the columns. This is a basic scenario to show you examples, in practical life, you may encounter many complex scenarios as well as a large data set. Now let’s begin our first method.

1. Finding Similar Text in Two Columns Using the SEARCH Function

While finding text that is similar to the measuring text you need to keep in mind that it may not be exactly the same at times. There can be differences between them. The most frequent differences we have noticed among the texts are case sensibility. For you, word Play and play can be the same thing. Yes, they are almost the same, the only difference is the case. These two are similar for computing tools. To find the similarities we can take the help of the SEARCH function in Excel. Let’s imagine a scenario, where you need to check whether a particular text matches the text from another column.

Here we have two columns Student Name and Summary. We will check whether there are similarities between the elements of these two columns. Our formula will be something like below.  Now, follow the steps below to use the formula to find similar text in two columns in Excel.

Steps:

• Write the formula in Excel in cell D5.

• Then, press Enter. You will get the result like this.

• Now, use the Fill Handle to apply the formula for the rest of the cells. We will get the following result.

• The highlighted cells were not matched, so the result here is Mismatch.

Read More: Find Duplicates in Two Columns in Excel (6 Suitable Approaches)

2. Extracting Similar Text Using a Combination of IF & LEN Functions

You may need to extract a similar text once you have known that it will be found in that column. Let’s have a scenario, where you need to find and fetch a similar text.

We are adapting the scenario using the previous example. In addition, we are going to fetch the text that has been matched. To extract the text we can apply any of three functions ( the LEFT function, the RIGHT function, and the MID function) depending on the searched text position. Let’s look at the LEFT function.

2.1 Use of LEFT Function for Extracting Similar Text

In our scenario, at first, we need to check whether there is a match or not. For that, the IF function will be used.  Now, follow the steps below to apply the formula to find similar text in two columns in Excel.

Steps:

• Write the following formula in Excel.

• Now, to get results for other cells, use the Fill Handle to fill up the formula in those cells.

• Keep in mind, here our text was on the left side so we can use the LEFT function without any hesitation. But your text could be somewhere within a long string. You need to find the position of the desired similar text and start extracting from there. To do so, the MID function will be helpful.

Read More: Formula to Find Duplicates in Excel (6 Suitable Examples)

Similar Readings

• How to Filter Duplicates in Excel (7 Suitable Ways)
• Excel Find Duplicate Rows Based on Multiple Columns
• How to Find Duplicate Rows in Excel (5 Quick Ways)
• Excel Find Duplicates in Column and Delete Row (4 Quick Ways)
• How to Compare Two Excel Sheets for Duplicates (5 Quick Ways)

2.2 Use of MID Function for Extracting Similar Text

It is quite similar to the previous method. Only we will use the MID function in the place of LEFT function. Now follow the steps to apply the function.

Steps:

• Write down the following formula in E5.

• The formula will be similar to the previous one, the only change will be, within the MID function we need to tell the starting position of the text. To state the starting position, we will use the SEARCH function.
• Now, click Enter button. You will see the following result.

• Now, use the Fill Handle to fill up the remaining cells with the formula.

• Notice that, we have changed the Summary string a bit for Lord and Alan. The similar texts are now not at the left but rather at the end. But our formula still works!

You can also use the RIGHT function in a similar manner if your searched keywords are on the right side.

Read More: How to Find Duplicate Values in Excel Using Formula (9 Methods)

3. Finding Values Using Similar Text in Two Columns

You may need to find value using a similar text. There can be many circumstances. To keep things simple, let’s imagine a circumstance where you are given a student list and the scholarship holders list.

The scholarship holder list consists of the Student ID. You can see a couple of ids are typed differently compared to the original id. Now we need to find the name of the student using these ids. We will do this using two approaches. Let’s look at the first approach.

3.1 Using a Combination of INDEX and MATCH Functions

This time, our task will be done using the INDEX-MATCH functions. Let’s follow the steps below.

Steps:

• Write down the following formula in H5.

• Here inside the INDEX function, we set the Student Name column as the find_array.  The MATCH function for declaring the row number. Within the MATCH function, we have inserted the lookup_value and lookup_array using the UPPER function so that the anomalies of the Upper Case and Lower Case can be overcome. 0 for stating the Exact Match. 
• Use CTRL + SHIFT + ENTER to execute the formula since it’s an array formula.

• Now, again use the Fill Handle to copy to formula on the rest of the cells below.

You can also use the LOWER function as well instead of the UPPER function. The UPPER function would compare after converting the text into upper-case and the LOWER function would compare converting into lower-case.

Read More: How to Use COUNTIF Formula to Find Duplicates (5 Easy Ways)

3.2 Applying VLOOKUP Function

You can use the VLOOKUP function instead of the INDEX-MATCH function. The VLOOKUP  function is an Excel function to look up data in a table organized vertically. Since we are dealing with columns we can use this function.  Now, follow the steps below to apply this formula.

Steps:

• Write the following formula in H5.

• Here, the VLOOKUP function will look up the text of G5 in the first column of B5:C14 array and return the corresponding 2nd column (2) value from the same row.
• Now, press Enter key.

• After that, auto-fill the rest of the cells using Fill Handle.

Like the previous example, we can use the LOWER function instead of the UPPER function.

Read More: How to Vlookup Duplicate Matches in Excel (5 Easy Ways)

Thing to Remember

• Use the first method only when you need to confirm whether a text is contained in the worksheet or not.
• If you need to extract the text, you can use the 2nd method.
• Only if you need to extract other texts than the searched one, you can use the 3rd method.

Conclusion

That’s all for today. We have tried listing the ways of finding similar text in two columns in Excel. Hope you will find this helpful. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here.

Related Articles

• How to Find Duplicates in Excel and Copy to Another Sheet (5 Methods)
• Excel VBA to Find Duplicate Values in Range (7 Examples)
• How to Find Duplicates in a Column Using Excel VBA (5 Ways)
• Use VBA Code to Find Duplicate Rows in Excel (3 Methods)
• How to Find Duplicates without Deleting in Excel (7 Methods)
• Find Duplicates in Two Different Excel Workbooks (5 Methods)
• How to Find Duplicates in Excel Workbook (4 Methods)