SOLVED

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

Copper Contributor

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:

image.png

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:

image.png

Any and all help in this matter would be greatly appreciated!

3 Replies

@excelzero 

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

@excelzero 

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

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

@excelzero 

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

View solution in original post