How to Identify Active and Inactive Candidates in Excel and Power Query
Question details
The user needs to create a dynamic solution to track candidate participation across exam sessions, labeling those in the latest sessions as 'Active' and others as 'Inactive'.

- Product
- Excel / Power Query
- Device & OS
- not provided
- Scenario
- Managing candidate exam records where new sessions are continuously added, requiring automatic status updates without manual recalculation.
- Observed behavior
- A system that automatically groups candidates, sorts by the newest sessions, and accurately outputs 'Active' or 'Inactive' status based on participation in the two most recent sessions.
Ensure you have an Office version that supports Dynamic Array functions (such as Microsoft 365) or verify that your Power Query add-in is enabled and updated.
Use Excel Dynamic Array Functions
Utilize modern Excel dynamic array functions like LET, UNIQUE, and SORT to automatically calculate and spill candidate status.
This approach uses the LET function to define variables for your data range, extracting unique dates and names, and then checks attendance using COUNTIFS. It dynamically builds a results table with headers and calculates if a candidate attended either of the two most recent sessions.
Identify the columns containing your exam dates and candidate names. For example, assume your dates are in Column A and names in Column B (A2:B1000).
Select a blank cell where you want the output table to begin and input the formula: =LET(data, A2:B1000, col_1, INDEX(data,, 1), col_2, INDEX(data,, 2), dates, TOROW(SORT(UNIQUE(col_1),, -1 ), 1), names, TOCOL(SORT(UNIQUE(col_2)), 1), results, COUNTIFS(col_1, dates, col_2, names), VSTACK(HSTACK("Name", dates, "Status"), HSTACK(names, IF(results, "x", ""), BYROW(TAKE(results,, 2), LAMBDA(r, IF(OR(r), "Active", "Inactive"))))))
Press Enter. The formula will automatically spill the data into neighboring cells, creating a matrix of candidates, their session attendance marked with 'x', and their calculated 'Active' or 'Inactive' status.

Group and Sort using Power Query
Use Power Query to clean and transform your dataset, which is ideal for older versions of Excel or exceptionally large datasets.
Manage Candidate Records Dynamically with WPS Spreadsheet
WPS Spreadsheet offers powerful data processing capabilities, including full support for modern dynamic array functions and advanced data filtering. Easily calculate candidate statuses and automate your reporting without heavy manual work.
- 1. Open your dataset: Launch WPS Spreadsheet and open the .xlsx file containing your candidate and exam session data.
- 2. Apply dynamic formulas: Select the target cell for your new table and paste the provided LET formula to automatically generate the active/inactive matrix.
- 3. Format and highlight: Use WPS Spreadsheet's Conditional Formatting from the Home tab to automatically highlight 'Active' candidates in green for better visibility.

Frequently Asked Questions
Why is my dynamic array formula returning a #NAME? error?
This usually happens if you are using an older version of Excel that does not support newer functions like LET, VSTACK, or HSTACK. You will need a compatible version like Excel for Microsoft 365 or WPS Office to use these specific formulas.
How can I change the formula to check the last 3 sessions instead of 2?
In the LET formula provided, locate the TAKE function segment: TAKE(results,, 2). Change the '2' to '3' to evaluate the candidate's participation across the three most recent exam sessions.
Does Power Query update automatically when new exam sessions are added?
Power Query does not update in real-time. Whenever you add new exam sessions to your source table, you must right-click the loaded Power Query output table and select 'Refresh', or go to the Data tab and click 'Refresh All' to calculate the new statuses.
Can I highlight active and inactive candidates with different colors?
Yes. Select the 'Status' column in your generated table, navigate to Home > Conditional Formatting > Highlight Cells Rules, and create a rule where cell value equal to 'Active' is formatted with green fill, and 'Inactive' with red fill.




