How to Return a Member ID in WPS Spreadsheets When First and Last Names Match
Question details
The user needs to look up a member ID from a master worksheet when both first and last names match, using a formula in WPS Spreadsheets.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has a master worksheet with member IDs and a separate worksheet where first and last names are entered; they want to return the matching member ID automatically.
- Observed behavior
- The desired state is a formula that returns the matching member ID, or a blank when no matching name combination is found.
Make sure the master worksheet contains the Member ID in one column and the first and last names in separate adjacent columns. Also confirm that the lookup names on the other worksheet are spelled and spaced exactly as they appear in the master list.
Use XLOOKUP with a Combined First-and-Last-Name Key
This is the recommended solution when you need a clean, formula-based lookup that matches both first and last name before returning the member ID.
The XLOOKUP function can combine first and last names into a single lookup key. Adding a separator such as "|" helps avoid ambiguous matches that can occur when names are simply joined together.
The example below assumes the master sheet is named Master, with Member ID in column A, First Name in column B, and Last Name in column C.
Open the worksheet where the first name and last name are entered, then select the cell where you want the member ID to appear.
Type =XLOOKUP(B2&"|"&C2,Master!B2:B7&"|"&Master!C2:C7,Master!A2:A7,"") and replace the cell references and sheet name with your actual ranges.
Press Enter to apply the formula. If the first and last name combination exists in the master worksheet, WPS Spreadsheets returns the matching member ID; otherwise it returns an empty string.
Return a Matching Member ID with XLOOKUP in WPS Spreadsheets
Use the XLOOKUP function in WPS Spreadsheets to combine first and last names into one lookup key, then return the matching member ID from a master worksheet without extra helper columns.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and open the workbook that contains both the lookup worksheet and the master worksheet.
- 2. Select the result cell: Click the cell where the member ID should be returned.
- 3. Enter the XLOOKUP formula: Type =XLOOKUP(B2&"|"&C2,Master!B2:B7&"|"&Master!C2:C7,Master!A2:A7,"") and adjust the references to match your data layout.
- 4. Complete the lookup: Press Enter. WPS Spreadsheets returns the matching member ID or a blank when no match is found.
Frequently Asked Questions
What formula returns a member ID when both first and last names match?
Use XLOOKUP with a combined key, for example: =XLOOKUP(B2&"|"&C2,Master!B2:B7&"|"&Master!C2:C7,Master!A2:A7,""). This matches the combined first and last name in the master worksheet and returns the corresponding member ID.
Why should I add a separator between first and last names?
A separator such as "|" prevents ambiguous concatenated matches. For example, combining "Ann" and "Lee" without a separator creates "AnnLee", which could also be created by other first and last name combinations.
What does the formula return when no member ID is found?
When no matching first and last name combination is found, the formula returns an empty string because the last argument of XLOOKUP is set to "". You can replace that argument with another value, such as "Not found", if needed.
Does WPS Spreadsheets support XLOOKUP?
Yes, current versions of WPS Spreadsheets support XLOOKUP. If it is not available, update WPS Office to the latest version or use an INDEX and MATCH array formula as an alternative.
Can this formula reference first and last names on different worksheets?
Yes, use the sheet name followed by an exclamation mark before the range, as in Master!B2:B7. Make sure the sheet name is spelled exactly as it appears in the workbook.
