Using INDEX/AGGREGATE to return multiple answers

[J_MAW]

Hi

I am trying without success to return multiple answers using Index / Aggregate.

I have a list of Action Points and they are either Complete, Not Started, Progressing, Off Track. 200427 Sample File.xlsx

I have got one formula working ( please see attached - CELL G5) but when I copy it across it returns same result (CELLS H5, I5) I would expect to see H5 = 1024 I5 = 1026. It also returns #NUM where a criteria is not in the INDEX (ie Not Started)

I therefore tried to use INDEX/AGGREGATE from a previous formula (though the original formula contained 2 criteria) but it keeps returning #VALUE.

All help would be most appreciated.

Many thanks

 

[Fluff]

How about, in G5 copied down & across
=IFERROR(INDEX($X$2:$X$26,AGGREGATE(15,6,(ROW($X$2:$X$26)-ROW($X$2)+1)/($W$2:$W$26=$B5),COLUMNS($G5:G$6))),"")

 

[tyija1995]

Hey, your INDEX SMALL formula is fine except for the final part with the k value, you have used $V$1 which is absolute reference, if you drag across columns you want this relative to the column, use COLUMNS($V$1:V$1) for example then drag across.

 

[J_MAW]

How about, in G5 copied down & across
=IFERROR(INDEX($X$2:$X$26,AGGREGATE(15,6,(ROW($X$2:$X$26)-ROW($X$2)+1)/($W$2:$W$26=$B5),COLUMNS($G5:G$6))),"")

Hi Fluff

Thank you so much for your quick reply.

That worked perfectly.

J

 

[Fluff]

Glad we could help & thanks for the feedback

 

[J_MAW]

Hey, your INDEX SMALL formula is fine except for the final part with the k value, you have used $V$1 which is absolute reference, if you drag across columns you want this relative to the column, use COLUMNS($V$1:V$1) for example then drag across.

Hi Tyija1995

Thank you for taking the time to reply. I'd already used Fluff's recommendation but I'll keep a note of your suggestion for future reference.

 

[tyija1995]

Awesome, yeah it was just the last parameter needed cleaning from your original formula, though INDEX AGGREGATE is nicer in my opinion as it doesn't need CSE I believe.

If you have access to O365, then you could use FILTER to achieve the above, a nice little note to add if you have/get O365 in the future.