How to Match Multiple Criteria and Return Attendance Status in Excel
Question details
The user needs a formula to look up an attendance status by matching a person's email and a specific workshop or cohort header.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking workshop attendance by cross-referencing participant emails with dynamic workshop headers.
- Observed behavior
- The user needs to retrieve an attendance value based on two variables, while realizing formulas cannot search by cell formatting like a gray fill color.
Ensure your data across both sheets is clean, and that the email addresses and headers match exactly without any hidden trailing spaces.
Use VLOOKUP with XMATCH for Dynamic Column Matching
Combine VLOOKUP and XMATCH to search vertically for the email and horizontally for the workshop header.
Formulas in Excel or WPS Spreadsheet look up values based on data, not cell formatting. To track attendance, make sure your raw data uses text values (like 'Y' or 'Present') rather than relying solely on a gray background color.
Click the cell where you want the attendance status to appear (for example, cell D4 in 'Sheet 1 Attendance').
Enter the following formula: =IFNA(VLOOKUP($C4,'Sheet 2 Workshop attended'!$B$2:$G$20,XMATCH('Sheet 1 Attendance'!D$3,'Sheet 2 Workshop attended'!$B$1:$G$1)),"").
Modify $C4 to reference the cell containing the email on your current sheet, and update 'Sheet 2 Workshop attended'!$B$2:$G$20 to the full data range containing emails and attendance.
Update 'Sheet 1 Attendance'!D$3 to the specific workshop header cell, and 'Sheet 2 Workshop attended'!$B$1:$G$1 to the header row of your source sheet.
Press Enter to get the result, then click and drag the fill handle at the bottom-right of the cell to apply the formula across your attendance tracker.
Use WPS Spreadsheet to Match Multiple Criteria Fast
WPS Spreadsheet fully supports advanced lookup functions like VLOOKUP, XLOOKUP, and XMATCH, making it incredibly easy to track attendance and cross-reference data across multiple sheets. It is a highly efficient, free tool that handles complex data processing smoothly.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your attendance data.
- 2. Select the target cell: Click on the specific cell in your tracking sheet where the status should be displayed.
- 3. Enter the formula: Type in your combined VLOOKUP and XMATCH formula and press Enter to return the attendance value.
- 4. Fill the tracker: Use the drag handle to quickly fill the formula down the column and across rows for all participants.
Frequently Asked Questions
Why is my VLOOKUP returning an #N/A error?
An #N/A error usually means the formula couldn't find an exact match for the lookup value. Check for trailing spaces in your email addresses or headers, and ensure your lookup range includes the correct columns.
Can I use XLOOKUP instead of VLOOKUP and XMATCH?
Yes, you can use nested XLOOKUP formulas to perform a two-way lookup. An XLOOKUP inside another XLOOKUP can match both row and column criteria, often providing more flexibility than standard VLOOKUP.
Is it possible to return a badge icon directly using a formula?
No, Excel and WPS Spreadsheet formulas can only return data values like text or numbers. However, you can use Conditional Formatting with Icon Sets based on the value the formula returns to display a badge dynamically.
