How do you copy an array formula down a column while changing one value?

Hello,

I have an array formula that I would like to apply to an entire column while changing one value, see below:

My formula is: =INDEX(N2:N7,MATCH(MAX(N(INDIRECT("'"&N2:N7&"'!E2"))),N(INDIRECT("'"&N2:N7&"'!E2")),0)) and I want each descending cell to have one change as seen below (changing the 'E' value):

=INDEX(N2:N7,MATCH(MAX(N(INDIRECT("'"&N2:N7&"'!E3"))),N(INDIRECT("'"&N2:N7&"'!E3")),0))
=INDEX(N2:N7,MATCH(MAX(N(INDIRECT("'"&N2:N7&"'!E4"))),N(INDIRECT("'"&N2:N7&"'!E4")),0))
=INDEX(N2:N7,MATCH(MAX(N(INDIRECT("'"&N2:N7&"'!E5"))),N(INDIRECT("'"&N2:N7&"'!E5")),0))

Any help is appreciated. Please see workbook.

Thanks!

Hi,

try this:

=INDEX($C$2:$C$7,MATCH(MAX(N(INDIRECT("'"&$C$2:$C$7&"'!A"&ROWS($G$1:G2)))),N(INDIRECT("'"&$C$2:$C$7&"'!A"&ROWS($G$1:G2))),0))