How to Match Names in Excel Where Spelling Differ (8 Methods)

Using IF to match name spelling differ

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.

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.

Sample Datasheet

Download Workbook to Practice

8 Methods to Match Names in Excel Where Spelling Differ

1. Match Names Where Spelling Differ in the 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

Using not equal sign to differ names

  • 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

Using not equal sign to differ spelling names

Later, You can use Fill Handle to AutoFit the formula for the rest of the cells of the column.

Using not equal sign to differ spelling names

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","")

Using IF to match name spelling 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.

Using IF to match name spelling differ

Finally, you can use Fill Handle to AutoFit the formula for the rest of the cells.

Using IF to match name spelling differ

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")

Using IF Formula both Matches and Differences

  • Whenever you are done with the typing formula just press ENTER.
  • It will show the result as either Differ or Match.

Using IF Formula both Matches and Differences

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.

Using IF Formula both Matches and Differences

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.

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.

Using COUNTIF to Match Differences

  • 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.

Using COUNTIF to Match Differences

If you want to see the values for the rest of the cells you can use Fit Handle to AutoFit the formula.

Using COUNTIF to Match Differences

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.

Matching case-sensitive Differences

  • To apply the formula in the selected cell press, ENTER.
  • Here, you will see the different names and also the match’s name.

Matching case-sensitive Differences

For seeing the result for all the cells use Fit Handle to AutoFit the formula. It will show only the exact match’s name as Match.

Matching case-sensitive Differences

6. Highlight 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.

Highlight Differences

It will pop up a dialog box from there select Use a formula to determine which cell to format.

Highlight Differences

Now type the formula =B5<>E5. Then set the Format. Finally, click OK.

Highlight Differences

In the selected cell it will show the Format I have already chosen for different names.

Highlight Differences

You can use AutoFit formula here also to fit the Highlighted Rules.

Highlight Differences

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.

Matching Differences of Two Columns

After selecting Manage Rules it will pop up a dialog box. Now select New Rule from there and click OK.

Matching Differences of Two Columns

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.

Matching Differences of Two Columns

It will pop up another dialog box. Now select the formula and click OK.

Highlight Differ Name of Two Column

Comparing two columns the cells of the B column are Highlighted.

Highlight Differ Name of Two Column

You can apply it for the other column E also.

Again open the Home tab >> Go to Conditional Formatting >> select Manage Rules.

Highlight differ name of two column

It will pop up a dialog box from there select New Rule then click OK.

Matching Differences of Two Columns

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.

Highlight Differ Name of Two Column

Another dialog box will pop up. Now select the formula there and click OK.

a dialog box to highlight differ two column

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.

Highlight differ name of two column

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.

Using Go To Special

A dialog box will pop up. From there select the option Row Differences.

Finally, click OK.

Using Go To Special

The names of the first B column will be Highlighted then I used Red Color fill.

Using Go To Special

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.

Practice Sheet

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.

 

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo