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

**Method 1**### 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.

**=B6<>C6**

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

**Method 2**### 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.

**=IF(B6<>C6,”Differ”,”Match”)**

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

**Method 3**### 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.

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

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

**Method 4**### 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.

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

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

**Method 5**### 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

**=B6<>C6**

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

**Method 6**### 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

**=COUNTIF($B$6:$B$22, $C6)>0**

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

**Method 7**### 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.