Formula to return values from variable rows and columns

Question

Some rows' values are conditionally usable, other not. Gray color in conditional formatting shows rows where values are disabled. I use =SUMPRODUCT(($AD$5:$AD$15>0)*X56:X66)  to sum values in usable rows. Green paint shows the best sum and rank 1 for enabled rows' values. If i change some values, this affects range AD5:AD15 and consequently which row's values are usable and the best sum can be in another column.

My problem is that i don't know how to use numbers in green column (in this example numbers 15, 16, 17) for another formula. Or at least how to show these numbers in another row without empty cells between them.

Answer 1

To get them into adjacent cells, array-enter (enter using Ctrl-Shift-Enter)

=IFERROR(INDEX($56:$66,SMALL(IF($AD$5:$AD$15>0,ROW($AD$5:$AD$15)-4),ROW(A1)),MATCH(1,$68:$68,FALSE)),"")

and copy down. If you want o copy accross instead of down, change ROW(A1) to COLUMN(A1) and enter using Ctrl-Shift-Enter.

Answer 2

Thanks. I knew that there had to be MATCH and INDEX. I did never think about SMALL and IFERROR. Thanks a lot!