SOLVED

# Help: Creating a List Based on Two Values From a Data Set

Copper Contributor

# 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!

3 Replies

# Re: Help: Creating a List Based on Two Values From a Data Set

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)
)``````
best response confirmed by excelzero (Copper Contributor)
Solution

# Re: Help: Creating a List Based on Two Values From a Data Set

=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.

# Re: Help: Creating a List Based on Two Values From a Data Set

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!
1 best response

Accepted Solutions
best response confirmed by excelzero (Copper Contributor)
Solution

# Re: Help: Creating a List Based on Two Values From a Data Set

=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.