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

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

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

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

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

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

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

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

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

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

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

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.

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.

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

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

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

**Find Matches or Duplicate Values in Excel (8 Ways)****Find Duplicates in Two Columns in Excel (6 Suitable Approaches)****Finding out the number of duplicate rows using COUNTIF formula****How to Find Matching Values in Two Worksheets in Excel (4 Methods)****How to Highlight Duplicate Rows in Excel (3 Ways)****How to Compare Rows in Excel for Duplicates****How to Find, Highlight & Remove Duplicates in Excel****How to Find & Remove Duplicate Rows in Excel****Excel Top 10 List with Duplicates****Excel Formula to Find Duplicates in One Column**