Tanzim
There may be times when you need to know easily how to match two names within Excel where the spelling differ. For example, you may need to use an Excel formula or VBA for this purpose. Today, we’ll show you how to match names in Microsoft Excel where spelling differ with various methods and lots of examples.
📁 Download Excel File
To prepare this article, we used an Excel workbook, which is available for download. Additionally, you can edit and customize the data you enter while viewing the results.
Learn to Match Names in Excel Where Spelling Differ with These 7 Easy Methods
We used a small dataset to clearly demonstrate the steps. There are roughly 17 rows and 2 columns in the dataset. We are starting with all cells in General format. We have two distinct columns for each dataset: Football Team and Cricket Team.
1. Matching Names in Each Row
Using the not equal sign ‘<>’ is an easy way to compare strings in Excel. You can do it by following these steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, we make a new column for the results and add the formula for comparing each cell, as shown below.
- Then we drag down the formula. As shown below, Excel will declare the output as FALSE if the comparison doesn’t work, and TRUE if it does.
2. Inserting IF Function
We can also use IF Function here to compare strings in Excel. So, an IF statement can lead to one of two things. If your comparison is True, you get the first result. If your comparison is False, you get the second result. You can do it by following these steps.
⬇️⬇️ STEPS ⬇️⬇️
- We make a new column for the results and add the formula for comparing each cell, as shown below.
- Then we drag down the formula. As shown below, Excel will indicate the result here as Differ if the comparison works, and Match if it does not.
3. Matching Differences with COUNTIF Function
You can match names with different spellings by using the COUNTIF called function. This function looks for the two-column values that are the same and the ones that are different. Depending on the value in one specific column, it checks to see if the value in the other column matches or does not match. We will also use IF Function to compare strings in Excel. So, an IF statement can lead to one of two things. If your comparison is True, you get the first result. If your comparison is False, you get the second result. You can do it by following these steps.
⬇️⬇️ STEPS ⬇️⬇️
- We make a new column for the results and add the formula for comparing each cell, as shown below.
- Then we drag down the formula. As shown below, Excel will show the output here as “No match in Football Team” if the comparison works, and a blank space if it does not.
🔨 Formula Breakdown 👉 Here, COUNTIF($C:$C, $B6)=0 will look for the value of B6 in the range of C6:C22. Then return the position of the value. 👉 The IF function will return the value as “No match in Football Team” if the result is found False. Otherwise the result will be blank if match is found.
4. Case-Sensitive Situation
Using the EXACT function is also an easy way to compare strings in Excel. Returns TRUE if two text strings are identical, FALSE otherwise when comparing two text strings. Although it ignores formatting discrepancies, EXACT is case-sensitive. You can do it by following these steps.
⬇️⬇️ STEPS ⬇️⬇️
- We make a new column for the results and add the formula for comparing each cell, as shown below.
- Then we drag down the formula. As shown below, Excel will show the correct result as Match if the comparison works, and Differ if it does not.
🔨 Formula Breakdown
👉 Here, EXACT(B6, C6) will match for the value of B6 with C6. Then return the result.
👉 The IF function will return the value as “Differ” if the result is found False. Otherwise the result will be “Match” if match is found.
5. Utilizing Highlight Differences
I’ll look to compare two strings for similarity in Excel in the whole set of data by using Conditional Formatting. See the steps below for how to do that.
⬇️⬇️ STEPS ⬇️⬇️
- First, we choose cell D6 to find duplicates in the whole set of data.
- Second, we choose Conditional Formatting again from the top Home tab, and then we choose New Rules from the second drop-down menu.
- In this case, to use a formula Under “Select a Rule Type,” choose “Use a formula to determine which cells to format”.
- Then, put the following formula in the type box
- Then, after using the above formula, we click the Format button to change the way the cells look.
- After setting the criteria for highlighting, we click OK to close the dialog box.
- Last, you’ll be able to see the duplicates, which will be highlighted in the data set like in the image below.
- Now we drag down the formula and the final result will look like this.
6. Finding Differences Between Two Columns
Here I’ll look for the difference between Two Columns in Excel in the whole set of data by using Conditional Formatting. We can match names with different spellings by using the COUNTIF function. This function looks for the two-column values that are the same and the ones that are different. Depending on the value in one column, it checks to see if the value in the other column matches or not. See the steps below for how to do that.
⬇️⬇️ STEPS ⬇️⬇️
- First, we choose cell range B6:B22 to find duplicates in the whole set of data.
- Second, we choose Conditional Formatting again from the top Home tab, and then we choose New Rules from the second drop-down menu.
- In this case, to use a formula Under “Select a Rule Type,” we choose “Use a formula to determine which cells to format”.
- After that, we put the following formula in the type box
- Then, after using the above formula, we click the Format button to change the way the cells look.
- After setting the criteria for highlighting, we click OK to close the dialog box.
- Last, you’ll be able to see the duplicates, which will be highlighted in the data set like in the image below.
- Now we do the same for column C. Final result will look like this.
7. Utilizing Go To Special Feature
Here I’ll look for a difference between Two Columns in Excel in the whole set of data by utilizing Go To Special feature. See the steps below for how to do that.
⬇️⬇️ STEPS ⬇️⬇️
- First, we choose cell range B6:B22 and C6:C22 to find duplicates in the whole set of data.
- Second, we choose Go To Special. you can find It from the drop-down menu again from the Find & Select from the Home.
- Then a new dialogue box will come. Here we select the Row differences. Then we press OK.
- Here will be our result. Marked cells are different.
📄 Important Notes
🖊️ In Excel, the ribbon is used to access various commands. You can change a lot of things in the options dialogue window, like the ribbon, formulas, proofing, saving, and so on. So the appearance of worksheets on your device could be different from ours.
🖊️ A practice workbook is given so that you can practice yourself.
🖊️ All these exercises and tutorials are done on Microsoft Office 365. Some functions may be unavailable in previous versions.
🖊️ At the side of each Excel worksheet file, there is space where they can practice.
📝 Takeaways from This Article
📌 The reader will be able to match two names in Microsoft Excel where the spelling differ with various methods.
📌 Readers can match names where spelling differ in the same row
📌 Readers can insert the IF Formula for both matches and differences
📌 They can also put COUNTIF to Match Differences
📌 Viewers will be able to match case-sensitive Differences
📌 Readers are able to highlight differences and match differences in two columns.
📌 Finally, viewers can use the “Go To Special” feature to match names in Microsoft Excel where spelling differ.
Conclusion
That concludes today’s session. These are the methods for using Excel to match names in excel where spelling differ. We are hopeful that this article will be extremely beneficial to you. Don’t forget to leave your thoughts and questions in the comments section and explore our website, ExcelDen, the best Excel solutions provider.
