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.

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.

## Practice Workbook

You are welcome to download the workbook from the link below

## Useful Text Functions

**1. SEARCH**** **

The** SEARCH** function returns the location of one text string inside another. The syntax for the **SEARCH **function

`SEARCH (find_text, within_text, [start_num])`

**find_text: **The text to find.

**within_text:** The text to search within.

**start_num:** Starting position in the text to search. This is an optional field, defaults to 1.

**SEARCH** returns the position of the first character of *find_text* inside *within_text. *

We will leave the Microsoft Support site link for you to know more about the function.

**2. UPPER**

The** UPPER **function converts a text string to all uppercase letters.

`UPPER (text)`

**text: **The text that you want to convert to uppercase

The **UPPER **function doesn’t affect the numbers, punctuation, and spaces.

To know more about the function visit the Microsoft Support site.

**3. LOWER**

The** LOWER **function converts a text string to all uppercase letters.

`LOWER (text)`

**text: **The text that you want to convert to lowercase

The **LOWER **function doesn’t affect the numbers, punctuation, and spaces.

To know more about the function visit the Microsoft Support site.

## 3 Ways to Find Similar Text In Two Columns

### 1. Finding Matches with 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

`IFERROR(IF(SEARCH(find_text,within_text),true_value),error_value)`

You may think why we are using the **IFERROR **function?

The **SEARCH **function returns the position when starting off the text once matches are found, but when no match? Then the **SEARCH **function returns an error value.

To eradicate that error we are using the **IFERROR **function here. And the *error_value *here will be the value if we don’t find matches, simply the *false_value. *

Write the formula in Excel.

Here within the **SEARCH **function, we have inserted the find_text (*Student Name*) and the within_text (*Summary). *

This **SEARCH **function is within an **IF **function. Once the **SEARCH **function finds matches it becomes **TRUE, **we have set the true_value to *Match. *So, when it finds a similar value, Excel will show *Match *as result.

And we have already discussed the usefulness of the **IFERROR **function. We have set our if_false value *Mismatch* using this function.

The formula returns the true_value here since we have a match.

You can see, though in the *Summary *column it was written ‘*alan’ *instead of *Alan, *the formula produces the resulting *Match. *

Write the formula for the rest of the rows here.

The highlighted cells were not in the match so the result here is *Mismatch.*

### 2. Extracting The Similar Text

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 have an Excel function called **LEFT. **

The **LEFT **function extracts a given number of characters from the left side of a supplied text string.

`LEFT (text, [num_chars])`

**text: **The text from which to extract characters.

**num_chars:** The number of characters to extract, starting on the left side of the text. This is an optional field. If you don’t provide it will assume 1 by default.

In our scenario, at first, we need to check whether there is a match or not. For that, the **IF **function will be used.

The formula will be

`IF(logic_check for match, LEFT(text,length),if_false_value)`

Write the formula in Excel.

First, we check whether there is a match or not using the *Match/Mismatch *column.

Then, within the **LEFT **function, we have set the text from which we need to extract the matching text and the length.

We use the **LEN **function, which provides the length of a given text. Within this, we have set the length of our desired text, so that Excel can understand how much to be extracted.

It gave us the text as the final result.

Write the formula for the rest of the rows.

Apart from the *Mismatch *row, it provided the text. And for the *Mismatch *row, the outcome is *Not available. *

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.

`MID (text, start_num, num_chars)`

**text:** The text to extract from.

**start_num:** The location of the first character to extract.

**num_chars:** The number of characters to extract.

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.

Write the formula in Excel.

Within the **MID **function, we have inserted the text, then the starting position using the **SEARCH **function, then the length. The procedure is the same as the previous one. And we have found the text.

We have changed the *Summary *string a bit for *Lord. *The similar text at the end. Though our formula fetches the text.

You can choose the **LEFT, RIGHT, **or **MID **considering your demand.

### 3. Find value using Similar Text Matches 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.

Our task will be done using the **INDEX-MATCH **functions.

`INDEX(find_array,MATCH(lookup_value,lookup_array, 0))`

The **MATCH** function is used to locate the position of a lookup value in a row, column, or table.

The **INDEX** function returns the value at a given location in a range or array.

Write the formula in Excel.

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 similarities can be overcome. 0 for stating *Exact Match. *

Use **CTRL + SHIFT + ENTER **to execute the formula since it’s an array formula.

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

Using the **LOWER **function we have derived the answer here.

You can use the **VLOOKUP **function instead of the **INDEX-MATCH **function.

**VLOOKUP** is an Excel function to look up data in a table organized vertically. Since we are dealing with columns we can use this function.

`VLOOKUP(lookup_value,lookup_array,column_number)`

Write the formula in Excel.

Here we have set the lookup_value using the **UPPER **function, then the lookup_array and 2 is the column number, as the *Student Name* is in the second column of our *lookup_array. *

You can use the **LOWER **function here as well instead of the **UPPER. **

Now write any of the formulas for the rest of the rows.

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