How to Find All Weeks Associated with a Person in Excel
Question details
The user needs to extract every holiday week in which a selected person appears from a schedule grid.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Filtering a list of dates or weeks from a 2D schedule table based on the presence of a specific employee's name.
- Observed behavior
- Requires a dynamic array formula for modern Excel versions, or a helper-column workaround because older Excel versions cause compatibility issues with functions like FILTER and BYROW.
Verify your Excel version before proceeding, as the most efficient dynamic array functions (FILTER and BYROW) are only available in Microsoft 365 and Excel 2021 or newer.
Use FILTER and BYROW Functions in Excel 365
For Microsoft 365 users, dynamic array formulas offer a clean, single-cell solution to extract matching weeks without the need for additional columns.
The FILTER function allows you to extract data based on criteria, while BYROW lets you apply a logical test across entire rows in your schedule matrix. Combined, they can instantly return all weeks where a person's name appears.
Identify the key ranges in your worksheet. Let 'weekCommencing' be the column containing your week labels, 'holiday' be the grid containing the scheduled names, and 'person' be the cell where you input the name you want to search for.
Select an empty cell where you want the resulting list of weeks to start. Type the formula: =FILTER(weekCommencing, BYROW(holiday=person, OR))
Press Enter. The formula will evaluate the grid and automatically 'spill' the matching weeks down the column.
Use a Helper Column Method for Older Excel Versions
If you are using an older version of Excel that does not support the FILTER or BYROW functions, you must use a helper column to flag the correct rows before extracting the data.
Use Advanced Dynamic Arrays in WPS Office
WPS Spreadsheet fully supports modern dynamic array functions like FILTER and BYROW. You can easily analyze complex schedules and extract specific data without worrying about the version compatibility issues found in older spreadsheet software.
- 1. Open your schedule workbook: Launch WPS Spreadsheet and open the .xlsx file containing your employee holiday schedule.
- 2. Select the output cell: Click on the blank cell where you want the extracted list of weeks to appear.
- 3. Input the FILTER formula: Type the formula =FILTER(weekCommencing, BYROW(holiday=person, OR)), replacing the named ranges with your actual cell references.
- 4. Press Enter to spill results: Hit Enter on your keyboard. WPS Spreadsheet will automatically process the matrix and display all matching weeks instantly.
Frequently Asked Questions
Why does my FILTER formula return a #NAME? error?
The #NAME? error usually occurs if your current version of Excel does not support the FILTER or BYROW function. These functions are only available in Microsoft 365 and Excel 2021 or newer. If you are on an older version, use the helper column method instead.
How exactly does the BYROW function work in this scenario?
BYROW takes an array (like your holiday schedule grid) and applies a logical condition to each row individually. In this formula, it checks if the specified person's name exists anywhere in that row, returning an array of TRUE and FALSE values that the FILTER function then uses to extract the correct weeks.
Can I search for multiple people at the same time?
Yes, but you will need to modify the formula. Instead of a simple equals sign (holiday=person), you can use the MATCH or COUNTIF functions within the BYROW logic to check if any names in the row intersect with a list of target names.
What does a #SPILL! error mean when using dynamic arrays?
A #SPILL! error means the formula has successfully found multiple results, but there is existing data or text in the cells directly below it preventing the results from displaying. Clearing the blocking cells will resolve the error.
