How to Match Attendance Code Names to Participant Names in WPS Spreadsheets
Question details
The user needs to link barcode-generated attendance code names such as SWIM5 and TF12 to participant first names and show only the selected participant's attendance, while keeping other participant records hidden.

- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- An attendance tracker uses barcode-generated team codes. The user wants to enter or scan a code and see the matching participant's first name and attendance without exposing other participants.
- Observed behavior
- The code name should resolve to a participant first name, and only that participant's attendance record should be displayed, with the lookup table protected from unauthorized viewing.
Create a secure lookup table that contains each unique code name in one column and the matching participant first name in the next column. Confirm that the attendance data uses the same code names exactly, with no extra spaces or hidden characters.
Use XLOOKUP to Match Code Names and FILTER to Show Only the Selected Participant's Attendance
Use this method when you need a dynamic, formula-based lookup that displays one participant's attendance without exposing the rest of the list.
XLOOKUP returns the first name associated with a scanned code. FILTER then limits the attendance rows to that participant, so the result updates automatically when the code changes.
Launch WPS Office, open your attendance workbook, and add a sheet named Lookup with code names in column A and participant first names in column B.
In the cell where the first name should appear, enter =XLOOKUP(scan_code,Lookup!A:A,Lookup!B:B,"Not found") and replace scan_code with the cell containing the scanned barcode code.
In an empty results area, enter =FILTER(A2:D100,C2:C100=scan_code,"No attendance found") to return only the matching participant's attendance rows.
Right-click the Lookup sheet tab, choose Protect Sheet, set a password, and allow only selected cells to be edited. This keeps the mapping table secure.

Use COUNTIFS to Validate Attendance Without Revealing Other Records
Use this method when you need a simple yes/no or total attendance check for one code without displaying the full attendee list.
Match Attendance Codes to Participant Names Securely in WPS Spreadsheets
WPS Spreadsheets can complete this lookup and secure display flow using XLOOKUP, FILTER, or COUNTIFS. It keeps the mapping table protected and works with existing Excel attendance workbooks.
- 1. Open your attendance workbook in WPS Spreadsheets: Download and install WPS Office, then open the Excel .xlsx attendance file in WPS Spreadsheets.
- 2. Create and protect the lookup table: Add a sheet named Lookup, list code names and participant names, then right-click the tab and choose Protect Sheet to secure it.
- 3. Enter the lookup and filter formulas: Use =XLOOKUP() in a visible results cell and =FILTER() in the results area to return only the chosen participant's attendance.
- 4. Verify the displayed result: Change the scanned code and confirm that only the selected participant's first name and attendance rows appear; fix any mismatched codes.

Frequently Asked Questions
Can WPS Spreadsheets use XLOOKUP like Excel?
Yes. WPS Spreadsheets supports XLOOKUP in modern versions. Open an .xlsx workbook, click a results cell, go to Formulas > Lookup & Reference, or type the formula directly.
How do I hide other participants' records from the results?
Use FILTER to return only rows where the code column equals the scanned code, and protect or hide the lookup sheet and raw attendance sheet.
What should I do if the barcode code has extra spaces?
Use the TRIM function in a helper column or clean the source data. For example, =TRIM(A2) removes leading and trailing spaces before XLOOKUP matches the code.
Can I protect the code-to-name mapping table in WPS Spreadsheets?
Yes. Right-click the mapping sheet tab, choose Protect Sheet, set a password, and restrict editing to specific cells or leave it locked.
Can I open an existing Excel attendance workbook in WPS Office without losing formulas?
Yes. WPS Spreadsheets is compatible with .xlsx files and preserves common Excel functions including XLOOKUP, FILTER, and COUNTIFS.




