Shamima Sultana
Comparing data in Excel rows is common while doing any data analysis task. In this article, I’ve explained eight methods to match names in Excel where spelling does differ. You will be able to find out if the same names exist for two different purposes or not.
Overview
In the following image, you will find an overview of the whole article.
I used a datasheet to make these methods’ explanations clearer. I used a dataset of two different banks having Name and Account Number. The same person may have two different accounts in different banks.
Download Workbook to Practice
8 Methods to Match Names Where Spelling Differ in Excel
In the following, I have described 8 simple methods to match names where spelling differ in Excel. Follow the methods below.
1. Match Names Where Spelling Differ in Same Row
To match spelling different names, you can use the basic not equal sign ‘<>’.
- First, select the cell where you want to show the spelling differs names.
- Here, I selected the H5 cell to place the result.
- Now, you can write the formula in the formula bar or into your selected cell.
- Type the formula
=B5<>E5
- Finally press ENTER
- If the value of different columns but the same rows differ it will show True otherwise FalseÂ
- In the Match Differ column, it will show TRUE as I selected B5 and E5 row where name spelling is different
Later, You can use Fill Handle to AutoFit the formula for the rest of the cells of the column.
2. Using IF Formula to Match Spelling Differ
You can use the IF function to show the name spelling differences.
- First, select the cell where you want to place your resultant value. I selected the H5 cell
- After that, you can type the formula in the cell or in the formula bar which one prefers. The formula is
=IF(B5<>E5,"Differ","")
- Now, press ENTER to run the formula.
- It will show the result as Differ when the compared names’ spelling is different. If spelling matches it will keep the cell empty.
- Finally, you can use Fill Handle to AutoFit the formula for the rest of the cells.
3. Using IF Formula Both Matches and Differences
In case you want to know both matches and different values you also can use the IF function.
To use this formula for both matches and different values, first, select the cell where you want to keep your result.
- I selected the H5 cell to apply the formula.
- You can use either the cell or the formula bar.
- Then, type the formula
=IF(B5<>E5,"Differ","Match")
- Whenever you are done with the typing formula just press ENTER.
- It will show the result as either Differ or Match.
- Now, use the Fill Handle to AutoFit the formula for the rest of the cells. You will see that the value for matched names will be Match where different names will be Differ.
This method is similar to the 2nd method, I just added the text Match for if_false_value, where the names of spelling are not different.
Read More: Excel Find Matching Values in Two Columns
4. Using COUNTIF to Match Differences
You can use the COUNTIF function to match spelling differ in names. This function searches the match and different values of the two-column. Depending on one column it compares the other column value if the matches or not.
- First, select the cell where you want to apply the COUNTIF formula. I selected cell H5.
- Write the Formula
=IF(COUNTIF($E:$E, $B5)=0, "No match in Wells Fargo", "")-
- Here, I am comparing the name of the B5 cell with the names of the E column.
- After typing the formula press ENTER.
- It will show No match in Wells Fargo if the spelling of the name differs with Bank of America.
- If you want to see the values for the rest of the cells you can use Fill Handle to AutoFit the formula.
Similar Readings
- How to Vlookup and Pull the Last Match in Excel (4 Ways)
- Sum All Matches with VLOOKUP in Excel (3 Easy Ways)
- How to Match Data in Excel from 2 Worksheets
5. Matching Case Sensitive Differences
Whenever we want the exact matches of names, we can use case sensitive differences to see the exact name.
To find case_sensitive differences we can use the EXACT function.
- Select the cell where you want to place your different names. I selected the H5 cell.
- Type the Formula
=IF(EXACT(B5, E5), "Match", "Differ")-
- You can type the formula either in the selected cell or in the formula bar.
- To apply the formula in the selected cell press, ENTER.
- Here, you will see the different names and also the match’s name.
- For seeing the result for all the cells use Fill Handle to AutoFit the formula. It will show only the exact match’s name as Match.
Read More: How to Find Case Sensitive Match in Excel ( 6 Formulas)
6. Highlighting Differences
From the ribbon, you can use Conditional Formatting to show the spelling differ names highlighted.
For that first, select the cell you want to highlight for the spelling differences in names.
- I selected H5 cell
- Now, open the Home tab >> Go to Conditional Formatting >> Then Highlight Cells Rule >> Finally, select More Rules.
- It will pop up a dialog box from there select Use a formula to determine which cell to format.
- Now type the formula =B5<>E5. Then set the Format. Finally, click OK.
- In the selected cell it will show the Format I have already chosen for different names.
- You can use the AutoFill formula here also to fit the Highlighted Rules.
Here, I used Blue Accent for the differ spelling names and white empty cells are the match’s name.
7. Matching Differences of Two Columns
Using the COUNTIF function in Conditional Formatting you can Highlight the Differences between the two columns.
- To use that first, select the cells you want to Highlight.
- Now open the Home tab >> Go to Conditional Formatting >> select Manage Rules.
- After selecting Manage Rules it will pop up a dialog box. Now select New Rule from there and click OK.
- Again it will pop up a dialog box from there select Use a formula to determine which cell to format.
- Then type the formula
=COUNTIF($E$4:$E$11, $B4)>0 - After that, set the Format. Finally, click OK.
- It will pop up another dialog box. Now select the formula and click OK.
Comparing two columns the cells of the B column are Highlighted.
You can apply it for the other column E also.
- Again open the Home tab >> Go to Conditional Formatting >> select Manage Rules.
- It will pop up a dialog box from there select New Rule then click OK.
It will pop up another dialog box from there select Use a formula to determine which cell to format.
- Then type the formula
=COUNTIF($B$4:$B$11, $E4)>0- After that, set the Format. Finally, click OK.
- Another dialog box will pop up. Now select the formula there and click OK.
Just like before comparing two columns, the cells of the E column are Highlighted. Now you can see the different names Highlighted in both columns.
8. Using Go To Special
To use Go To Special from the ribbon first select the rows from which you want to determine differences.
- Then open the Home tab >> Go to Find & Select >> select Go To Special.
A dialog box will pop up. From there select the option Row Differences.
- Finally, click OK.
The names of the first B column will be Highlighted then I used Red Color fill.
Practice Section
I have given a practice sheet in the workbook just to practice the method I explained in this article. Download the given workbook to practice it by yourself.
Conclusion
In this article, I explained 8 methods to match names in Excel where spelling does differ. These different approaches will help you to find names in different spelling. Feel free to comment down below to give any kind of suggestions, ideas, and feedback.
