In our daily life, we need to match various data in daily life at the Corporate office, Government office, Survey, Industries, etc. Excel Find Similar Text in Two Columns is an inevitable top nowadays. We hope ‘Excel Find Similar Text in Two Columns’ will help you to match and play with data quickly.

## 📁 Download Excel File

To practice and get the overview, please download the excel file from here:

## Learn to Find Similar Text in Two Columns in Excel with These 7 Ways

we can find Similar Text in Two Columns in 7 quick approaches. Here We consider a dataset of a society containing **Winner 2021**, **Winner 2022, ID,** and winning** History of 2021**. We are going to use some formulas containing **IF**,** EXACT, COUNTIF, IFERROR, VLOOKUP, LEFT, MID, RIGHT, UPPER, **and** INDEX** functions**.** In this article ‘**Excel Find Similar Text in Two Columns**’, we will highlight to observe visually similar or mismatched values.

**Method**

### 1. Compare Two Columns for Matches and Differences

Comparing data of two years to match, there are some summon faces. Suppose we need to know the names who won in the past year 2021. We can do it in two ways.

**Matches and Differences**

#### 1.1 Find Similar Text in Two Columns in Excel

We can use the **IF** function to match the data row by row, you can get the values you are looking for. Follow the steps,

**⬇️⬇️**** STEPS ****⬇️⬇️**

- Like us, Select a blank cell i.e.
**D6**. - Insert the formula in
**D6**to match row by row

**=IF(B6=C6,”Match”,”No match”)**

**🔨 Formula Breakdown**

👉 Syntax of this formula, **=IF(Cell_comparison,”Statement_1″,”Statement_2″)**

👉 **IF** function generally dictates in cell D6, if **B6=C6**, deliver Match; Otherwise, No Match.

- Now, get the result at
**D6**.

- Like
**D6**, you need to use**Fill Handle**to get the output on the rest cells.

- Here comes the final results.

Therefore, We are capable of delineating match or mismatch values.

**📕 Read More: Find Duplicate Rows Based on Multiple Columns in Excel**

**Matches and Differences**

#### 1.2 Case-Sensitive Matches in Same Row

If we plan to exclude **case-sensitive** words, we may **EXACT** function to match exactly both spelling along with the letters’ **upper-case** or **lower-case**.

**⬇️⬇️**** STEPS ****⬇️⬇️**

- Like previously, Select a blank cell i.e.
**D6**. - Input the formula in
**D6**to match cell to cell.

**=IF(EXACT(B6, C6), “Match”, ” “)**

**🔨**** Formula Breakdown**

👉 General Syntax of this formula, **=IF(EXACT(REf_Cell_comparison),”Statement_1″,”Statement_2″) **

** 👉** The

**EXACT**function finds out the exact match without any changes between

**B6**and

**C6.**

👉 Finally, the** IF** function dictates in cell D6, if **B6=C6, **deliver** Match**.

- You will obtain the result at
**D6**. After that, using**Fill Handle**we get the final result.

Hector is the only performer who won in two consecutive years in a single segment where we considered case sensitivity.

**📕 Read More: 5 Ways to Find Duplicates in Column and Delete Row in Excel**

**Method**

### 2. Using COUNTIF Function to Find Similar Text in Two Columns and Output to Third in Excel

Let’s say, you need to match 2021 winners to 2022 winners just to make sure if there is anyone who won last year, also this year. The COUNTIF function can be a key to comparing between columns and making sure to another column.

**⬇️⬇️**** STEPS ****⬇️⬇️**

- Initially, Select a blank cell i.e.
**D6**. - Then, Input the formula containing
**COUNTIF**and**IF**in**D6**to match cell to column.

**=IF(COUNTIF($C:$C,$B6)=0,”No match in C”,””)**

**🔨**** Formula Breakdown**

👉 Combining **IF** and **COUNTIF**. Let’s get general **Syntax** of this formula, **=IF(COUNTIF(column_by_range,Cell_Index)=0,”statement_1″,”statement_2″)**

👉 The **COUNTIF **function counts the cell number based on certain criteria**.**

👉 **B6 **finds out values like itself in **column-C** and returns value **0**.

👉 Finally, the **IF** function dictates in cell D6, if **B6 is **unable to match to column **C, **deliver** No match in C**.

- By obtaining the result at
**D6**and using**Fill Handle**we achieve the final result.

Here Bonucchi, Payne, and Jammy is the winner of 2021 who is not the back-to-back winner of 2022.

**Method**

### 3. Compare Two Cell’s Text Using **SEARCH** Function

Suppose you have a set of data with names. One tells you to find matching from history. What to do? Using the **SEARCH** function can be your cup of tea in this case.

**⬇️⬇️**** STEPS ****⬇️⬇️**

- Firstly, Select a blank cell i.e.
**D6**. - Secondly, Input the formula containing
**IF**,**SEARCH**, and**IFERROR**in**D6**to match cell**B6**to find from the text of**C6**.

**=IFERROR(IF(SEARCH(B6,C6),”Match”),”Mismatch”)**

**🔨**** Formula Breakdown**

👉 General Syntax of the formula, **=IFERROR(IF(SEARCH(cell_1,cell_2),”statement_1″),”statement_2″)**

👉 **SEARCH** function search particular data within a cell or in a set of data. Here,** SEARCH(B6, C6) **means in **B6** is finding similar data to **C6**.

👉 If data is found in** C6**, then **IF** signals to make a statement as **Match**.

👉 Else it may signal an Error. But **IFERROR** makes the statement **Mismatch** instead of Error.

- By getting the result at
**D6**and using**Fill Handle**we finally achieve our result.

**Method**

### 4. Excel Formula to Find Similar Text in Two Columns and Return a Value

You can also get a specific text from some lines from another column. Comparing two columns first we will get our specific text and return it to another cell using **LEFT**, **RIGHT**, and **MID** functions.

**Excel Formula**

#### 4.1 Use of LEFT Function for Extracting Similar Text

The **LEFT** function generally relates the most left words with the similar text we are looking for. Let’s have a look.

**⬇️⬇️**** STEPS ****⬇️⬇️**

- Navigate a cell i.e.
**D6**. - Insert the following formula that counts words.

**=IF(D6=”Match”,LEFT(C6,LEN(B6)),”Not Available”)**

**🔨**** Formula Breakdown**

👉 General Syntax of the formula, **=IF(D6=”Match”,LEFT(cell_2,LEN(Cell_1)),”Not Available”)**

👉 **LEN** returns the no of characters in the string. Therefore, it returns the same no of characters that exist in **B6** and finds from the beginning of the line using the **LEFT** function; where the **RIGHT** function finds the value at the end of the line.

👉 ALso** IF** function works if a column like **D6** gets a **Match** value. Otherwise, it dictates Not Available.

- Finally, we get the result at D6 and using
**Fill Handle**we finally get the total result.

As **LEFT** function works with the top left function. If your desired value is not the first word, you will get garbage value. In this case, the **MID** function can be a valuable substitute.

**📕 Read More: 5 Ways to Create a Top 10 List with Duplicates in Excel**

**Excel Formula**

#### 4.2 Use of MID Function for Extracting Similar Text

Similarly, using the formula containing **IF, MID, and LEN** we will also get out the desired output.

**=IF(D6=”Match”,MID(C6,SEARCH(B6,C6),LEN(B6)),”Not Available”)**

But, Unlike **LEFT** or Right, the Use of the **MID** function gives us the best results.

**Excel Formula**

#### 4.3 Use of RIGHT Function for Extracting Similar Text

Also, the formula containing **IF, RIGHT, **and** LEN** can be a way to get out the desired output if the matched text is at the end of the line.

**=IF(D10=”Match”,RIGHT(C10,LEN(B10)),”Not Available”)**

As it matches the last word of the line. You can’t use it unless your desired text is at the end of the line.

**Method**

### 5. Compare One Cell with Entire Column Using INDEX and MATCH Functions Combined

Suppose, One asks to find a specific value from a lot of data. You can use the **MATCH** function to match exactly and the **INDEX** function to run an array and return the value. To look up the value you need to use the **UPPER** function too.

**⬇️⬇️**** STEPS ****⬇️⬇️**

- Firstly, select a cell i.e.
**J6**. - Insert the following formula that counts words.

**=INDEX(E6:E11,MATCH(UPPER(H6),UPPER(F6:F11),0))**

**🔨**** Formula Breakdown**

👉 Formula syntax, =**INDEX(array,MATCH(UPPER(lookup_value),UPPER(lookup_range),0))**

👉 With the **INDEX** function, we declare the **array** to find out the names of the winner matching their ID.

👉 **UPPER** is also used to find the data of **H6** in the specified data of **column F**.

👉 **MATCH** function finds the exact values and **0** for starting the exact match.

- Finally, we get the result at
**D6**and using**Fill Handle**we finally get the total result.

**Method**

### 6. Using VLOOKUP Function to Find Similar Text in Two Columns in Excel

**VLOOKUP** function dedicates to looking for a value within a specific array. To compare columns one can use **VLOOKUP** along with the **UPPER** function.

**⬇️⬇️**** STEPS ****⬇️⬇️**

- Initially, navigate a blank cell i.e.
**J6**. - After that, Input the following formula in
**J6.**

**=VLOOKUP(UPPER(H6),E6:F11,2)**

**🔨**** Formula Breakdown**

👉 Formula syntax, **VLOOKUP(UPPER(lookup_value),range,by_column)**

👉 **UPPER** is also used to find the data of** H6** in the specified data range** E6** to **F11**.

👉 **VLOOKUP** function indicates to lookup of the data from the range and takes from the **2nd** column.

- After all, we get the result at
**D6**and using**Fill Handle**we finally get the output.

**Method**

### 7. Compare and Highlights Similar Texts in Two Columns

Sometimes we need to get an insight into data visually. From two columns we can get similar texts, unique texts, and duplicate texts. The first step is common for these three.

The first step is common for these three. Please follow the steps,

**⬇️⬇️**** STEPS ****⬇️⬇️**

- First, click on
**the Home**tab from**Top Ribbon**. - Next, click on
**Conditional Formatting**. - Then, click on
**New Rules**. - After that, you will get a box of the
**New Formatting Rule**.

**Highlight Texts**

#### 7.1 Similar Text in Each Row

To get highlighted similar text in each row, follow the below steps.

**⬇️⬇️**** STEPS ****⬇️⬇️**

- Initially, Select the column where you want the highlight. Here we choose
**B6**to**B11**. - Then, Select
**Use a formula to determine within cells to format**. - Next, Use the formula
**$C6=$B6**. - After that, Choose a
**format**color. - Finally, Click on
**Enter**.

Therefore, **green-marked** data indicates similar texts in two columns.

**📕 Read More: 9 Unique Cases to Compare Rows for Duplicates in Excel**

**Highlight Texts**

#### 7.2 Unique Entries

To get unique text in each row, follow the below steps.

**⬇️⬇️**** STEPS ****⬇️⬇️**

- First, Select the entire range of data from
**B6**to**C11**. - Secondly, Select
**Format unique or duplicate values**in**New Formatting Rule**. - Thirdly, Select
**Unique**. - After that, Choose a
**format**color. - In the end, Click on
**Enter**.

Finally, gray-marked data indicates unique values.

**Highlight Texts**

#### 7.3 Duplicates in Two Columns

Following the below steps, you will get duplicate texts in two columns.

**⬇️⬇️**** STEPS ****⬇️⬇️**

- First, Select the full dataset
**B6**to**C11**. - next, Select
**Format unique or duplicate values**in**New Formatting Rule**. - Then, Select
**duplicate**. - After that, Choose a
**format**color. - In the end, Click on
**Enter**.

Here, **Blue marked** data indicates **duplication**.

## 📄 Important Notes

🖊️ Be cautious while using **LEFT** or **RIGHT** functions. These two functions only consider the character of the beginning and end of the lines.

🖊️ Inspect **functions** and **syntax** carefully during the use of the formulae.

## 📝 Takeaways from This Article

📌 In this article we used **IF, EXACT, COUNTIF, IFERROR, VLOOKUP, LEFT, MID, RIGHT, UPPER, **and** INDEX **functions.** **

📌 We demonstrated every formula briefly so that it may also help during other applications.

## Conclusion

In this article, we tried to demonstrate every possible way to find similar text between two or more columns. Hope that you enjoyed your learning. For queries and any kind of suggestions please comment below. Also for more, please visit **www.ExcelDen.com**