Function Problems

Compare Employee Names With and Without Middle Initials in Excel

Ayan MasoodAyan Masood Nov 18, 2026 869 views

Question details

Compare two employee name lists when only one includes middle initials, and identify names without a match.

Compare Employee Names With and Without Middle Initials in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
One employee list contains first names, middle initials, and last names, while another list omits the middle initials.
Observed behavior
The desired result is a match status for each name based on its first and last names, regardless of a middle initial or period.
Before you start

Place the two name lists in columns A and B with headers in row 1. These formulas assume names are in first-name-first order, with at least a first and last name; check suffixes and multi-part surnames separately.

Solution 1Recommended

Create name keys and flag unmatched employees in Excel 365

Use first and last names as comparison keys, then check both lists with COUNTIF.

This method removes periods and extra spaces and ignores everything between the first and last name. It does not remove other punctuation, so review inconsistent punctuation before comparing.

1
Create the first key

In C2, enter =IF(A2="","",LOWER(TEXTBEFORE(TRIM(SUBSTITUTE(A2,".",""))," ")&" "&TEXTAFTER(TRIM(SUBSTITUTE(A2,".",""))," ",-1))). This creates a lowercase key from the first and last words in A2.

2
Create the second key

Copy C2 to D2 so the formula references B2. Select C2:D2 and drag the fill handle down through the rows containing names.

3
Check the first list

In E2, enter =IF(A2="","",IF(COUNTIF($D$2:$D$100,C2)=0,"No match","Match")) and fill it down. Change 100 to the last row of the second list.

4
Check the second list

In F2, enter =IF(B2="","",IF(COUNTIF($C$2:$C$100,D2)=0,"No match","Match")) and fill it down. Change 100 to the last row of the first list, then filter columns E and F for No match.

Create name keys and flag unmatched employees in Excel 365
Review ambiguous matches: Two employees with the same first and last names will match even if they are different people. Verify such cases with an employee ID or another identifying field.
Compare names in WPS Spreadsheets

Find Unmatched Employee Names in WPS Spreadsheets

Use helper columns in WPS Spreadsheets to compare first and last names while ignoring middle initials, periods, and extra spaces.

  1. 1. Open the lists: Open your workbook in WPS Spreadsheets and place the two first-name-first lists in columns A and B, starting in row 2.
  2. 2. Clean the names: In C2, enter =IF(A2="","",TRIM(SUBSTITUTE(A2,".",""))). Copy C2 to D2 to clean the name in B2, then fill both formulas down.
  3. 3. Build comparison keys: In E2, enter =IF(C2="","",LOWER(LEFT(C2,FIND(" ",C2)-1)&" "&TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",LEN(C2))),LEN(C2))))). Copy E2 to F2, then fill both columns down. Each name must contain at least a first and last word.
  4. 4. Flag both lists: In G2, enter =IF(A2="","",IF(COUNTIF($F$2:$F$100,E2)=0,"No match","Match")). In H2, enter =IF(B2="","",IF(COUNTIF($E$2:$E$100,F2)=0,"No match","Match")). Adjust each range to cover the other list and fill the formulas down.
  5. 5. Review the results: Use Data > Filter to show No match in columns G and H. Review names with suffixes, multi-part surnames, or identical first and last names manually.
Flag names missing from either employee list.Open and save Microsoft Excel XLSX files.Use familiar spreadsheet formulas and a lightweight interface.Work with a free Office alternative.
microsoft office alternative - wps office

Frequently Asked Questions

Will a name with a middle initial match one without it?

Yes. The comparison keys use the first and last words, so Jane M. Smith and Jane Smith both produce the key jane smith.

Why does a single-word name produce an error?

The key formulas look for a space between at least a first and last name. Add the missing name or review that entry separately.

Do the formulas distinguish employees with the same first and last names?

No. They ignore middle names and initials, so identical first-and-last-name keys match. Use an employee ID or another field to verify those records.

How do I find names missing from either list?

Create a COUNTIF check for each list against the other list's key column, then use Data > Filter to display rows marked No match in each results column.