I would like to create a named range that creates the range based on 2 conditions, criteria 1 and criteria 2

Question

I would like to create a named range that is dependent on 2 conditions. I have written the code that creates a list based on Criteria 1 being "aa" as in the example. I would like to create the same list that uses "aa" but also has a "y" in criteria 2. Attached a picture for clarity. Here is the formula used for the first criteria:

=OFFSET(INDEX(C2:C13,MATCH("aa",A2:A13,0),0),0,0,COUNTIF(A2:A13,"aa"),1)

Answer 1

Best ist Filter function, if you have:

=FILTER(C4:C15;(A4:A15="aa")*(B4:B15="y"))

otherwise the orange solution with a few helper columns:

=IF(AND(A4="aa";B4="y");C4;"")
=IF(E4<>"";ROW();"")
=SMALL($F$4:$F$15;ROWS($F$4:F4))
=IFERROR(INDEX($E$4:$F$15;MATCH(G4;$F$4:$F$15;0);1);"")