Function Problems

Replace Multiple Names in One Excel Cell with NTID Lookup Values

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 869 views

Question details

Convert multiple name and job-title entries in one cell into their corresponding NTIDs, separated by semicolons.

Replace Multiple Names in One Excel Cell with NTID Lookup Values
Product
Microsoft Excel
Device & OS
not provided
Scenario
A cell contains several entries that must be matched against a name-to-NTID lookup table.
Observed behavior
The desired result is a list of matching NTIDs in one cell, separated by semicolons.
Before you start

Create a lookup table with each complete name and job-title entry in Sheet2 column A and its NTID in column B. Check how entries are separated in the original cell; the example below assumes semicolons separate entries and do not appear within an entry.

Solution 1Recommended

Use a Helper Formula to Look Up and Join NTIDs

Use this method in Microsoft 365 when each entry is separated by a semicolon and matches a complete lookup-table entry.

The formula splits the source text, removes extra spaces around each entry, looks up its NTID, and joins the results. A formula cannot replace the contents of its own source cell in place.

If spaces or commas separate entries as well as parts of names or job titles, define an unambiguous entry delimiter before using this formula.

1
Check the source and lookup table

Put the source list in A2 and the matching full entries and NTIDs in Sheet2!A2:B100. For example, A2 could contain Jane Doe, Manager; John Smith, Analyst. Avoid empty entries or a trailing semicolon.

2
Enter the lookup formula

In an empty helper cell such as B2, enter =LET(items,TRIM(TEXTSPLIT(A2,";")),TEXTJOIN("; ",TRUE,XLOOKUP(items,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100,"not found"))). Adjust the lookup ranges to fit your table.

3
Check the results

Review B2 for the semicolon-separated NTIDs. If you see not found, compare the corresponding source entry with Sheet2 column A and correct any mismatch.

4
Replace the original text if needed

Copy B2, select A2, and use Paste Special > Values. After confirming the pasted result, you can remove the helper formula.

Use a Helper Formula to Look Up and Join NTIDs
Match the lookup key: The formula looks up the entire entry, including the job title. If your table contains names only, the entries must first be separated into names using a reliable rule.
Work with lookup lists in WPS

Convert Name Entries to NTIDs in WPS Spreadsheets

Use Text to Columns and VLOOKUP in WPS Spreadsheets to split a list, match entries to NTIDs, and join the results in a helper cell.

  1. 1. Prepare the lookup table: Open the workbook in WPS Spreadsheets. Put complete entries in Sheet2 column A and their NTIDs in column B.
  2. 2. Split a two-entry example: Copy the semicolon-separated source list from A2 to B2. Select B2, choose Data > Text to Columns, and use a semicolon delimiter to split the two entries into B2 and C2. Make sure C2 is empty first.
  3. 3. Look up each entry: In D2, enter =IFERROR(VLOOKUP(TRIM(B2),Sheet2!$A$2:$B$100,2,FALSE),"not found"). Fill the formula across to E2 to look up the second entry.
  4. 4. Join and verify the NTIDs: In F2, enter =D2&"; "&E2. Check for not found and correct any source entry that does not match the lookup table.
  5. 5. Paste the result as text: If you want to replace the original list, copy F2, select A2, and choose Paste Special > Values.
Look up multiple name and job-title entries against an NTID tableOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and data toolsWork with spreadsheets in the free WPS Office suite
microsoft office alternative - wps office

Frequently Asked Questions

Can an Excel formula replace the text in its own cell?

No. Enter the formula in a separate helper cell, then copy its result and use Paste Special > Values over the original cell if needed.

What if commas or spaces appear inside names and job titles?

Use a delimiter that does not occur within an entry, such as a semicolon, or establish another reliable parsing rule. Splitting on commas or spaces alone may break individual entries.

Why does the formula return not found?

XLOOKUP returns not found when an entry does not match Sheet2 column A. Check that the lookup key includes the same name and job title as the source entry and correct any spelling differences.

Can I use the Excel formula without Microsoft 365?

The formula requires Excel functions including TEXTSPLIT and XLOOKUP. If your version lacks them, use Data > Text to Columns to split entries and VLOOKUP in helper cells, then join the results.