WPS Spreadsheet: Replace Names with Aliases Using a Lookup Formula
Question details
The user needs to replace names in one worksheet with matching aliases from a lookup table on another worksheet.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A lookup table contains names and aliases on Sheet1, while the names to convert are listed on Sheet2. The user wants to update many entries without manually using Find and Replace.
- Observed behavior
- The names have not yet been converted to aliases, and manually replacing 189 entries would be time-consuming.
Confirm that the original names are in one column and their corresponding aliases are in the adjacent column on the lookup sheet. Check that the spelling and spacing of names match between the two worksheets.
Use XLOOKUP to return aliases automatically
Use this method when your WPS Spreadsheet version supports XLOOKUP and you want unmatched names to remain unchanged.
Assuming Sheet1 column A contains names, Sheet1 column B contains aliases, and Sheet2 cell A2 contains the name to convert, XLOOKUP can return the matching alias or keep the original name when no match exists.
Open Sheet2 and select the cell beside the first name, such as B2.
In the formula bar or directly in B2, enter =XLOOKUP(A2,Sheet1!A:A,Sheet1!B:B,A2), then press Enter.
Select the formula cell and drag its fill handle down the list, or use Home > Fill > Down, to convert all names.
Use VLOOKUP for broad compatibility
Use this method when XLOOKUP is unavailable or when the name and alias columns are arranged as the first two columns of the lookup range.
Use INDEX and MATCH for flexible lookup ranges
Use this alternative when you need the lookup and return columns to remain independent of their positions.
Replace a Large List of Names with Aliases in WPS Spreadsheet
WPS Spreadsheet lets you match names to aliases with XLOOKUP, VLOOKUP, or INDEX MATCH, then fill the formula down an entire worksheet. This avoids repetitive manual replacements and keeps the results linked to the lookup table.
- 1. Create the lookup table: In WPS Spreadsheet, place each original name in Sheet1 column A and its matching alias in Sheet1 column B.
- 2. Open the destination sheet: Go to Sheet2 and click the cell beside the first name, such as B2.
- 3. Enter a lookup formula: Type =XLOOKUP(A2,Sheet1!A:A,Sheet1!B:B,A2), or use =VLOOKUP(A2,Sheet1!A:B,2,FALSE) if XLOOKUP is unavailable.
- 4. Apply the formula to all rows: Press Enter, select the formula cell, and drag the fill handle down the list or choose Home > Fill > Down.
Frequently Asked Questions
How do I replace names with aliases in another WPS Spreadsheet worksheet?
Place names and aliases in two columns on Sheet1, then on Sheet2 enter =XLOOKUP(A2,Sheet1!A:A,Sheet1!B:B,A2) beside the first name. Fill the formula down the column.
What formula can I use if XLOOKUP is not available?
Use =VLOOKUP(A2,Sheet1!A:B,2,FALSE) when names are in Sheet1 column A and aliases are in column B. You can also use =INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0)).
How can I keep names that do not have an alias?
Use =XLOOKUP(A2,Sheet1!A:A,Sheet1!B:B,A2). The final A2 argument returns the original name when no match is found.
Why does my lookup formula return an error?
Check for spelling differences, extra spaces, and inconsistent text formatting between the two sheets. Also confirm that the sheet name and lookup columns in the formula are correct.
