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.

Finding Similar Text In Two Columns Using the SEARCH Function

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.
=IFERROR(IF(SEARCH(B5,C5),"Match"),"Mismatch")
  • Then, press Enter. You will get the result like this.

Finding Similar Text in Two Columns Using the SEARCH Function

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

Finding Similar Text in Two Columns Using the SEARCH Function

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

How Does the Formula Work?

  • SEARCH(B5,C5)

This will search the value of B5 (Michel) in cell C5. If it finds the searched text, it will return starting position of that text (1 in this case)

  • IF(SEARCH(B5,C5),”Match”) 

It returns the text “Match” if the result from the SEARCH function is any number.

  • IFERROR(IF(SEARCH(B5,C5),”Match”),”Mismatch”)

The whole formula returns the text “Mismatch” if the SEARCH function yields an error.


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.
=IF(D5="Match",LEFT(C5,LEN(B5)),"Not Available")

Extracting The Similar Text Using a Combination of IF & LEN Functions

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

Use of LEFT function for Extracting Similar Text

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

How Does the Formula Work?

  • LEN(B5)

The LEN function determines the length of the text that will be extracted.

  • LEFT(C5,LEN(B5))

The LEFT function extracts the same number of characters determined by LEN(B5) from left side of cell C5 

  • IF(D5=”Match”,LEFT(C5,LEN(B5)),”Not Available”)

The IF function returns the result of the LEFT function if the D5 contains the text “Match”, otherwise returns “Not Available”


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.
=IF(D5="Match",MID(C5,SEARCH(B5,C5),LEN(B5)),"Not Available")
  • 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.

Use of MID function for Extracting Similar Text

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


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.

Finding Values Using Similar Text in Two Columns

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.
=INDEX(C5:C14,MATCH(UPPER(G5),UPPER(B5:B14),0))
  • 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.

Finding Values Using Similar Text in Two Columns

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


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.
=VLOOKUP(UPPER(G5),B5:C14,2)
  • 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.

Finding Values Using Similar Text in Two Columns

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

Finding Values Using Similar Text in Two Columns

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


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.


Further Readings

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo