How to Use an Excel Array Formula to Return Active Projects by End Date
Question details
The user needs a dynamic Excel formula to extract and create a continuous list of projects with valid end dates that fall on today's date or later.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering and tracking active projects dynamically based on the current date, ensuring the result list updates automatically as time passes and projects change.
- Observed behavior
- The user wants the formula to generate a clean, continuous list of active projects by skipping rows with FALSE values or dates that have already passed.
Ensure your project data is organized neatly in columns without merged cells, and verify that the 'End Date' column contains actual date values rather than text strings.
Use an INDEX-MATCH Array Formula with SMALL and IF Functions
This comprehensive array formula filters out past dates and skips rows that don't match the criteria, creating a continuous list of active projects.
In older versions of Excel (like Office 2019) that do not support the dynamic FILTER function, you must use a traditional array formula. The combination of INDEX, SMALL, IF, and ROW evaluates the date logic for the entire range and returns the project names one by one as you drag the formula down.
Click the first cell where you want the filtered list of active projects to appear (for example, cell G3).
Enter the following formula, adjusting the ranges $B$3:$B$17 for your project names and $D$3:$D$17 for your end dates: =IFERROR(INDEX($B$3:$B$17,SMALL(IF(ISNUMBER($D$3:$D$17)*($D$3:$D$17>=TODAY()),ROW($B$3:$B$17)-MIN(ROW($B$3:$B$17))+1),ROWS($F$3:$F3))),"")
Instead of pressing Enter, press Ctrl + Shift + Enter simultaneously. Excel will automatically add curly brackets {} around the formula to indicate it is evaluating an array.
Click and hold the small square fill handle at the bottom-right corner of the cell, and drag it down to fill the formula into the remaining rows until empty cells appear.
Use a Basic IF Statement for Same-Row Evaluation
A simpler alternative if you only want to flag whether a project in a specific row is active, rather than condensing the results into a skipped-row list.
Easily Manage Project Data and Array Formulas in WPS Spreadsheet
WPS Office provides a fully-featured spreadsheet application that perfectly handles advanced array formulas, dynamic functions, and complex project tracking. It is a lightweight, highly compatible alternative to Microsoft Office.
- 1. Install WPS Office: Download and install the free version of WPS Office on your computer.
- 2. Open your workbook: Launch WPS Spreadsheet and open your existing project tracking workbook.
- 3. Enter the array formula: Select the target cell, type or paste your array formula, and press Ctrl+Shift+Enter.
- 4. Auto-fill the column: Drag the fill handle downwards to populate your dynamic list of active projects.
Frequently Asked Questions
Why does my array formula show an error or the wrong result?
Traditional array formulas utilizing INDEX and SMALL must be confirmed by pressing Ctrl+Shift+Enter. If you simply press Enter, the formula will not evaluate the logical arrays properly and will result in a #VALUE! error or incorrect data.
Is there a simpler way to return a list without blank rows in newer Excel versions?
Yes. If you have Microsoft 365 or a newer version of Excel that supports dynamic arrays, you can use the FILTER function instead. The formula would simply be =FILTER(B3:B17, D3:D17>=TODAY(), ""), which automatically spills the correct results without needing Ctrl+Shift+Enter.
How can I adjust this formula if my data starts on a different row?
You need to update the absolute references (like $B$3:$B$17 and $D$3:$D$17) to match your actual data range. The MIN(ROW(...))+1 portion of the formula is designed to automatically calculate the correct row offset, so you do not need to manually change the indexing logic.
