WPS Spreadsheet Lookup Formulas to Match Names and Return Team Numbers
Question details
The user needs to compare two name lists and return the matching team number beside each name in the first list.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Names are stored in List 1, while a second list contains names and their corresponding team numbers. The user wants to match the names and retrieve the team number automatically.
- Observed behavior
- The required team numbers are not yet populated next to the names in List 1; a lookup formula is needed to return them.
Confirm which column contains the names in List 1 and which columns contain the reference names and team numbers in List 2. Check that names use consistent spelling and remove unintended leading or trailing spaces before applying the formula.
Use XLOOKUP in WPS Spreadsheet to return matching team numbers
Use this method when your WPS Spreadsheet version supports XLOOKUP and you want an exact match with a blank result when no name is found.
XLOOKUP searches the reference name column and returns the corresponding value from the team-number column. The empty-string argument keeps unmatched rows blank instead of displaying an error.
In WPS Spreadsheet, click the first empty cell beside the name in List 1, such as B2.
Type =XLOOKUP(A2,$D:$D,$E:$E,"") and press Enter. In this example, A2 contains the List 1 name, column D contains the reference names, and column E contains team numbers.
Select the cell containing the formula and drag its fill handle down the result column, or copy and paste it into the remaining rows.
Review any blank results and compare the name spelling in List 1 with the reference list in column D.
Use VLOOKUP or INDEX and MATCH for older spreadsheet versions
Use one of these alternatives when XLOOKUP is unavailable or when you need compatibility with older Excel and spreadsheet files.
Match names and retrieve team numbers with WPS Spreadsheet
WPS Spreadsheet lets you compare name lists with XLOOKUP, VLOOKUP, or INDEX and MATCH. Enter a formula beside the first list, lock the reference ranges, and fill it down to return team numbers automatically.
- 1. Open the spreadsheet: Launch WPS Office, choose Spreadsheet, and open the workbook containing the two name lists.
- 2. Identify the lookup columns: Locate the List 1 name cell, the reference name column, and the team-number column before writing the formula.
- 3. Enter the lookup formula: Click the first empty result cell and enter =XLOOKUP(A2,$D:$D,$E:$E,""); adjust the cell and columns if your lists use different locations.
- 4. Copy the formula down: Press Enter, select the completed formula cell, and drag the fill handle through the remaining List 1 rows.
- 5. Save the completed workbook: Choose File > Save or press Ctrl+S to preserve the returned team numbers and formulas.
Frequently Asked Questions
What formula matches a name in List 1 and returns its team number?
If List 1 names are in column A, reference names are in column D, and team numbers are in column E, enter =XLOOKUP(A2,$D:$D,$E:$E,"") beside the first List 1 name and fill it down.
How can I prevent an error when a name is not found?
Use XLOOKUP's fourth argument as an empty string, as in =XLOOKUP(A2,$D:$D,$E:$E,""). With VLOOKUP or INDEX and MATCH, wrap the formula in IFERROR, such as =IFERROR(VLOOKUP(A2,$D:$E,2,FALSE),"").
What should I use if XLOOKUP is not available?
Use =IFERROR(VLOOKUP(A2,$D:$E,2,FALSE),"") when the reference names are in the leftmost column of the lookup range. You can also use =IFERROR(INDEX($E:$E,MATCH(A2,$D:$D,0)),"") for more flexible column placement.
Why does the lookup return a blank even though the name appears in both lists?
Check for spelling differences, extra spaces, hidden characters, or inconsistent punctuation. Compare the names directly and clean the source text before running the lookup again.
