Compare Employee Names With and Without Middle Initials in Excel
Question details
Compare two employee name lists when only one includes middle initials, and identify names without a match.

- 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.
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.
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.
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.
Copy C2 to D2 so the formula references B2. Select C2:D2 and drag the fill handle down through the rows containing names.
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.
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.

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

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.




