List if conditions met

[ddub25]

A	B	C
1	ON	1
2	OFF	3
3	ON	4
4	ON	
5	OFF	
6	OFF	
7	OFF	
8	OFF	
9	OFF	
10	OFF

Column A is a list of numbers that are either turned ON or OFF.

Column B indicates whether they are turned ON or OFF. As you can see, numbers 1, 3 and 4 are turned ON.

Column C lists the numbers that are currently turned ON. Can anyone help with a formula for Column C that will list these numbers that are turned ON in a consecutive manner?

 

[Weazel]

maybe something like...

=IFERROR(INDEX($A$1:$A$10,AGGREGATE(15,6,$A$1:$A$10/($B$1:$B$10="on"),ROWS($C$1:C1))),"")

 

[ddub25]

It must be equating to an error when I use that formula as all cells in Column C are empty. Any ideas why it is not outputting 1,3 and 4 in C1, C2, C3 respectively?

 

[Weazel]

not sure what the issue might be because its working for me...

C2=IFERROR(INDEX($A$1:$A$10,AGGREGATE(15,6,$A$1:$A$10/($B$1:$B$10="on"),ROWS($C$1:C1))),"")


you can try using

=IFERROR(INDEX($A$1:$A$10,SMALL(IF($B$1:$B$10="ON",ROW($A$1:$A$10)-ROW($A$1)+1),ROWS($D$1:D1))),"") control shift enter

as an alternative

Row\Col	A​	B​	C​
1​	1	ON	1​
2​	2	OFF	3​
3​	3	ON	4​
4​	4	ON
5​	5	OFF
6​	6	OFF
7​	7	OFF
8​	8	OFF
9​	9	OFF
10​	10	OFF

<tbody>
</tbody>

 

[ddub25]

Thanks Weazel, the 2nd formula seems to solve my problem.