Forum Discussion
Solved
Help: Creating a List Based on Two Values From a Data Set
Hi! Struggling with a rather basic issue: I need to pull the name of a class in a list based on "Active" status. Here is how the data is laid out now:
I want to be able to have a formula find all "Active" classes in the set of data above and have them be listed like so below:
Any and all help in this matter would be greatly appreciated!
=INDEX($A$2:$A$5,SMALL(IF($B$2:$B$5="Active",ROW($B$2:$B$5)-1),D2))
This formula works in my sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.
=INDEX($A$2:$A$5,SMALL(IF($B$2:$B$5="Active",ROW($B$2:$B$5)-1),D2))
This formula works in my sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.
- This worked, thanks! Had an iddue with it picking Select instead of Active, but changed the -1 to -2 and it's working accurately! Thanks again!
I suspect you are not using Excel 365, otherwise the task would be straightforward.
= LET( activeList, FILTER(name, status="Active"), seq, SEQUENCE(COUNTA(activeList)), HSTACK(seq, activeList) )
