Use Excel FILTER to Populate a Calendar from Employee and Date Criteria
Question details
The user needs to populate calendar cells with shift-group values by matching employee names and dates in a source table.
- Product
- Microsoft Excel for Microsoft 365
- Device & OS
- not provided
- Scenario
- A calendar has employee names in its headers and dates in its rows. The user wants each calendar cell to return the matching shift-group value from the source data.
- Observed behavior
- The calendar needs a formula that filters the shift-group range using both the employee-name criterion and the date criterion.
Confirm that columns Q, R, and S contain employee names, dates, and shift-group values, and that the calendar employee headers are in row 1 while its dates are in column U. Make sure your Excel version supports the dynamic-array FILTER function.
Use a two-criteria FILTER formula in the calendar
Use this solution when each calendar cell should return the shift-group value matching both an employee name and a date.
The formula compares the employee-name range with the calendar header and the date range with the calendar row. Multiplying the two TRUE/FALSE tests applies an AND condition, while the empty-string argument keeps unmatched cells blank.
In Excel, click the first cell where a shift-group result should appear, such as V2, beneath the employee header and beside the date row.
Type =FILTER($S$2:$S$1000,($Q$2:$Q$1000=V$1)*($R$2:$R$1000=$U2),"") and press Enter. This returns values from column S when column Q matches the employee header and column R matches the date in column U.
Select the formula cell and drag its fill handle to the right, or use Home > Fill > Right, so the employee-header reference changes for each calendar column.
Select the completed row and drag the fill handle downward, or use Home > Fill > Down, so the date-row reference changes for each calendar row.
Build a Criteria-Based Calendar in WPS Spreadsheets
WPS Spreadsheets can use the same FILTER formula to populate a calendar from employee and date criteria. Open your workbook, enter the formula in the first calendar cell, and fill it across and down while preserving Excel-compatible references.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the Excel workbook containing the source table and calendar.
- 2. Select the first calendar cell: Click the first calendar result cell, such as V2, where the shift-group value should appear.
- 3. Enter the formula: Type =FILTER($S$2:$S$1000,($Q$2:$Q$1000=V$1)*($R$2:$R$1000=$U2),"") in the formula bar and press Enter.
- 4. Fill across and down: Use the cell fill handle to copy the formula across the employee columns and down the date rows.
Frequently Asked Questions
What does the FILTER formula return in this calendar?
It returns values from $S$2:$S$1000, such as shift groups, when the employee name in $Q$2:$Q$1000 matches the calendar header and the date in $R$2:$R$1000 matches the calendar row.
Why are there two conditions multiplied in the formula?
The expression ($Q$2:$Q$1000=V$1)*($R$2:$R$1000=$U2) applies an AND condition. A row is included only when both the employee name and date match.
How can I keep unmatched calendar cells blank?
Use the third FILTER argument as an empty string: "". If no source row matches both criteria, the calendar cell remains blank instead of displaying an error.
What should I check if FILTER is not recognized?
Confirm that your Excel or WPS Spreadsheet version supports dynamic-array functions. Also check that the formula uses the correct list separator for your regional settings, such as commas or semicolons.
Can one calendar cell return multiple matching shift groups?
Yes. FILTER can spill multiple matching results into adjacent cells when more than one source row meets both criteria. Ensure the spill area is empty before entering the formula.
