I have been able to return the max value of F1 from all sheets by using this formula located in cell G13:
G13=MAX(ABC:XYZ!F1)
Now I also want to return the value of A5 from the sheet where the MAX was found in G13.
How do I write the formula for that?
TIA.
CodePudding user response:
A non-volatile
alternative, which references the maximum value you have already calculated in cell G13
:
=INDEX(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,ABC:XYZ!A5)&"</b></a>","//b"),MATCH(1,FREQUENCY(G13,ABC:XYZ!F1),0))
CodePudding user response:
Create a named range that includes all the sheet names.
Then using INDEX/MATCH and INDIRECT inside MAX and SUMIFS we get:
=INDEX(INDIRECT("'"&TRANSPOSE(Sheets)&"'!A5"),MATCH(MAX(INDIRECT("'"&TRANSPOSE(Sheets)&"'!f1")),SUMIF(INDIRECT("'"&TRANSPOSE(Sheets)&"'!F1"),"<>"),0))
Depending on one's version one may need to use Ctrl-Shift-Enter instead of Enter.
The HERE
is coming from Sheet2!A5
where Sheet2!F1
has 1000
in it. All other sheets have 1
in F1