Extracting all rows based on a value of cell without VBA

Question

I'm really struggling to find an answer to this as online I've really only found VBA solutions to this problem which isn't what I wish to learn how to do.

THE PROBLEM

BLOOD NAME  AGE GENDER
A   David   18  Male
B   Sarah   22  Female
O   Lucy    32  Female
AB  Steven  23  Male
O   John    11  Male
B   Mike    25  Male
AB  Paul    24  Male
O   Amy 23  Female
B   Drake   22  Female
O   Linda   11  Female

Very simply from the above dataset I wish to recreate this range but filter for only select BLOOD TYPE O.

MY ATTEMPTS

Started with a VLookup table however that stops at the first occurrence of O. Then tried incorporating IF/THEN/ELSE logic into a MATCH operand trying to locate the row numbers outputting to an array. (not gonna post my failed attempts) I did find a similarish problem online however they solved it via referencing the range manually using ROW(A1), ROW(A2) etc etc wasn't what I after.

Really want to learn how to do this type of iterative selections using Excel formulae only. Even if not solving the problem any direction towards resources where I can learn more about this type problem, would be still appreciated.

Answer 1

This does not use array formulas, but does use a helper column. Assuming data in cols A through D, in E2 enter:

=IF(A2="O",1+MAX($E$1:E1),"")

and copy down:

Each of the O rows is marked with a simple sequential value. This makes it easy for the usual MATCH() / INDEX() methods.

Pick some other cell and enter:

=IFERROR(INDEX(A:A,MATCH(ROWS($1:1),$E:$E,0)),"")

and copy this cell both across and down: