MD Tanvir Rahman
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.
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.
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
🔨 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
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.
🔨 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
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.
🔨 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.
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.
🔨 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.
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.
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.
🔨 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
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.
But, Unlike LEFT or Right, the Use of the MID function gives us the best results.
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.
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.
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.
🔨 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.
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.
🔨 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.
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.
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
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.
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
