Function Problems

How to Match Attendance Code Names to Participant Names in WPS Spreadsheets

Algirdas JasaitisAlgirdas Jasaitis Nov 13, 2026 868 views

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.

How to Match Attendance Code Names to Participant Names in WPS Spreadsheets
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.
Before you start

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.

Solution 1Recommended

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.

1
Open WPS Spreadsheets and create a lookup sheet

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.

2
Enter the XLOOKUP formula

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.

3
Add the FILTER formula for attendance

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.

4
Protect the lookup sheet

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 XLOOKUP to Match Code Names and FILTER to Show Only the Selected Participant's Attendance
Secure and dynamic result: The selected participant's name and attendance update instantly, while other records remain hidden behind the protected lookup table.
Direct solution in WPS Spreadsheets

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. 1. Open your attendance workbook in WPS Spreadsheets: Download and install WPS Office, then open the Excel .xlsx attendance file in WPS Spreadsheets.
  2. 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. 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. 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.
Matches barcode team codes to participant names with XLOOKUPDisplays only the selected participant's attendance with FILTERFully compatible with Microsoft Excel .xlsx attendance workbooksProtects the lookup table and personally identifiable informationFree, lightweight spreadsheet formulas without extra add-ins
microsoft office alternative - wps office

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.