How do you find the max in a particular cell across all sheets and return a different cell?

user_13062685 0
Viewed 35

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.

vlookup excel
2 Answers

Create a named range that includes all the sheet names.

enter image description here

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.

enter image description here

The HERE is coming from Sheet2!A5 where Sheet2!F1 has 1000 in it. All other sheets have 1 in F1

user_48515900

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))

user_170077040
Related