Array Formula Help...

[How_Do_I]

With an array formula like:

=IF(ROWS($G$3:G3)>$K$6,"",INDEX(Date,SMALL(IF(Taken=1,ROW(Date)-ROW(INDEX(Date,1))+1),ROWS($G$3:G3))))

How would I replace the name range "Taken" with a range like "$A$5:$D$50" and instead of the named range "Taken" how would I look in the 2nd column of $A$5:$D$50 for equal to 1 please?

Hope that makes sense...

 

[jasonb75]

Something like

=IFERROR($A:$A,AGGREGATE(15,6,ROW($B$5:$B$50)/($B$5:$B$50=1),ROWS($G$3:$G3)),"")

Does not need array confirmation.

 

[How_Do_I]

Hello Jason... My poor OP... I want to select which column by a dropdown and a column header match in "A4:D4"

 

[jasonb75]

Part of it was my error, I just noticed a big typo, INDEX( is missing from the formula

G1 is the dropdown.

=IFERROR(INDEX($A:$D,AGGREGATE(15,6,ROW($B$5:$B$50)/($B$5:$B$50=1),ROWS($G$3:$G3)),MATCH($G$1,$A$4:$D$4,0)),"")

I'm still not sure that it will be what you want thought, you've mentioned A:D for the result and B (second column of A:D) for the criteria. I've assumed that only the result column changes.

 

[How_Do_I]

Thanks Jason... I'll drop that in to my workbook tomorrow and have a look...