Fix Excel Lookup Formulas That Do Not Match Full Names Consistently
Question details
The user needs a lookup formula to reliably return a team name when matching a person's full name across two worksheets.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook uses INDEX and MATCH or XLOOKUP to match full names between worksheets and return the associated team name.
- Observed behavior
- The same full name may return a match in some rows but fail to match in others.
Confirm that the lookup value and the source-name column are intended to contain the same full-name format. Select a failed name and compare it with the source name in the formula bar to identify spaces, punctuation, or invisible characters.
Normalize full names before running the Excel lookup
Use this method when names look identical but INDEX and MATCH or XLOOKUP returns no match for certain records.
Lookup formulas require an exact text match unless configured otherwise. Leading, trailing, repeated, nonbreaking spaces, hidden characters, and inconsistent punctuation can make two visually identical names different.
Create a helper column on both worksheets so the lookup uses cleaned, standardized names rather than the original text.
On each worksheet, insert a helper column next to the full-name column. In the first data row, enter a formula such as =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) and replace A2 with the cell containing the full name.
Select the helper-column formula cell and drag the fill handle down, or double-click the fill handle, to clean every name in the list.
If one sheet contains punctuation that the other does not, add SUBSTITUTE to the helper formula. For example, use =SUBSTITUTE(TRIM(CLEAN(A2)),".","") to remove periods from names.
Change the lookup value and lookup array to the cleaned-name columns. For example, use =XLOOKUP(C2,Teams!$C:$C,Teams!$B:$B,"Not found") or use INDEX and MATCH with the normalized helper columns.
Click a name that previously returned an error and verify that its cleaned helper value is identical on both worksheets. The lookup should now return the related team name.

Force recalculation and enable automatic workbook calculation
Use this method when the cleaned values are correct but lookup results appear outdated or do not refresh after editing source data.
Standardize Names and Repair Lookup Matches in WPS Spreadsheets
WPS Spreadsheets provides familiar spreadsheet formulas for cleaning full names and matching related records across worksheets. Use helper columns with TRIM, CLEAN, SUBSTITUTE, XLOOKUP, or INDEX and MATCH to return the correct team for each person.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the name lookup.
- 2. Create cleaned-name columns: Insert a column beside each full-name list and enter =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) in the first helper cell.
- 3. Fill the formula for all names: Select the helper-cell formula and use the fill handle to copy it through the remaining rows on each worksheet.
- 4. Apply a lookup using cleaned values: Enter XLOOKUP or INDEX and MATCH using the helper columns as the lookup value and lookup range, then return the team column.
- 5. Refresh and verify results: Press Ctrl+Alt+F9 if needed, then test previously unmatched names to confirm their teams are returned.

Frequently Asked Questions
Why does XLOOKUP fail when the names appear to be identical?
XLOOKUP treats hidden spaces, nonbreaking spaces, invisible characters, and punctuation differences as different text. Create cleaned helper columns with TRIM, CLEAN, and, when necessary, SUBSTITUTE, then use those columns in the lookup.
How can I remove nonbreaking spaces from names in Excel?
Use SUBSTITUTE to replace CHAR(160), which represents a common nonbreaking space. For example: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))).
Should I use XLOOKUP or INDEX and MATCH for matching names between worksheets?
Use XLOOKUP when it is available because it directly specifies the lookup column and return column. Use INDEX and MATCH when working in an Excel version without XLOOKUP. Both require normalized lookup text for reliable exact matches.
How do I make Excel update lookup formulas automatically?
Go to Formulas > Calculation Options and select Automatic. If results are still delayed, press Ctrl+Alt+F9 to force a full workbook recalculation.
Can capitalization cause an Excel exact-match lookup to fail?
Standard XLOOKUP and MATCH exact matching are not case-sensitive. Failures are more commonly caused by extra spaces, hidden characters, different punctuation, or names stored in inconsistent formats.




