Creating a list from two columns without gaps

[fielg]

I have used the below array formula before to get all ID=8 in a separate list but now I have a new problem that I hope can be solved in a similar way but I cannot really figure out how..

Array formula used to get all rows with ID=8 in a new list without gaps.
=INDEX(ID,SMALL(IF(ID=8,ROW(ID)-ROW($A$1)+1),ROWS(AF$28:AF28)))

Now I have the following sheet:

In column A i have all ID:s
In column B i have an argument, YES or NO.

What I want to do is to get a list of all ID:s in column A that are marked with YES in a new list without any gaps. How can I do this? As column B can change quite a lot I want this as an array formula so it automatically updates the new list.

The list of ID:s are around 600 rows if that matters.

Thanks for your help!

[Fotis1991]

Try this Array formula

=IFERRORINDEX($A$2:$A$1000,SMALL(IF($B$2:$B$1000="YES",ROW(A$2:$A$1000)-1,ROW(A1))),"")

[e.isayev]

An array formula
=SMALL(IF($B$1:$B$25="YES",$A$1:$A$25),ROW(A1))
A=columns ID
B columns Yes no

[Fotis1991]

@ eisayew

Pls notice that usually the ID's are not only numbers.

[fielg]

Thank you so much Fotis1991! I think it was a small error in your array formula but I got it sorted, below is the one i finally used:

=IFERROR(INDEX($A$2:$A$1000,SMALL(IF($B$2:$B$1000="YES",ROW(A$2:$A$1000)-1),ROW(A1))),"")

Again, huge thanks! Didn't really get my head around this when I was trying to figure it out, nice to have this forum to fall back on!

[Fotis1991]

You are welcome.

Yes i missed a paranthesis! after -1 Nice that you found this.

[e.isayev]

You are right, I should use Index-match with small function. Thank you