Adapting a formula to find correct result

[Patcheen]

this formula does what i want by giving the correct result

=IFERROR(AGGREGATE(14,6,$A$5:$A$500/($C$5:$C$500=$Z$388)/($D$5:$D$500="yc")/($W$5:$W$500="home"),1),"")

but my prob is when i drag it down its all the one date

column A is date (this is the result i need returned) when all the criteria Met
column c is player
column Z is is the players name that must match C
Column D is yellow cards
Column W is Home

 

[jasonb75]

Try

=IFERROR(AGGREGATE(14,6,$A$5:$A$500/($C$5:$C$500=$Z$388)/($D$5:$D$500="yc")/($W$5:$W$500="home"),ROWS(X$1:X1)),"")

X$1:X1 should refer to the cell holding the formula before you drag down.

 

[Patcheen]

Try

=IFERROR(AGGREGATE(14,6,$A$5:$A$500/($C$5:$C$500=$Z$388)/($D$5:$D$500="yc")/($W$5:$W$500="home"),ROWS(X$1:X1)),"")

X$1:X1 should refer to the cell holding the formula before you drag down.

Does exactly what it says on the tin - Thank you so very much