• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to find a max/min value between sheets AND return the sheet name as well?

brianb343

New Member
I feel like this should be fairly simple, but I just can't find the right combo of functions to do both. Finding the max or min value between sheets is fairly straight forward, but how can you return the min/max value AND the sheet name which it came from?
=MAX(Sheet1!A2,Sheet2!A2,Sheet3!A2,Sheet4!A2) gives me the highest value for 'A2' between all four sheets; can this function be combined with an index/match or IF function to return which sheet the max value was found on? The max value and sheet name could be in two different cells; just trying to return them both on the summary sheet.
 

Hui

Excel Ninja
Staff member
Brianb

Firstly, Welcome to the Chandoo.org Forums

You can simplify: =MAX(Sheet1!A2,Sheet2!A2,Sheet3!A2,Sheet4!A2)
To: =MAX(Sheet1:Sheet4!A2)

Then in D2:D5 or somewhere else put a list of the Worksheet Names

Then use: =INDEX(D2:D5,MATCH(TRUE,COUNTIF(INDIRECT("'"&D2:D5&"'!A10"),D1)>0,0)) Ctrl+Shift+Enter

I assume that your max value using =MAX(Sheet1:Sheet4!A2) is in cell D1
.
 

Coffeebeans

New Member
Then use: =INDEX(D2:D5,MATCH(TRUE,COUNTIF(INDIRECT("'"&D2:D5&"'!A10"),D1)>0,0)) Ctrl+Shift+Enter

Could i ask what is the !A10 in this example? i get a N/A result or a Value result.

Thanks.
 

bosco_yip

Excel Ninja
Then use: =INDEX(D2:D5,MATCH(TRUE,COUNTIF(INDIRECT("'"&D2:D5&"'!A10"),D1)>0,0)) Ctrl+Shift+Enter

Could i ask what is the !A10 in this example? i get a N/A result or a Value result.

Thanks.
I think it is a typo, A10 should read as A2.

This formula return the Sheet name which have the MAX value.

=INDEX(D2:D5,MATCH(TRUE,COUNTIF(INDIRECT("'"&D2:D5&"'!A2"),D1)>0,0))
Ctrl+Shift+Enter

or,

try this non-CSE formula :

=INDEX(D2:D5,MATCH(1,INDEX(COUNTIF(INDIRECT("'"&D2:D5&"'!A2"),D1),0),0))

Regards
Bosco
 

Coffeebeans

New Member
I think it is a typo, A10 should read as A2.

This formula return the Sheet name which have the MAX value.

=INDEX(D2:D5,MATCH(TRUE,COUNTIF(INDIRECT("'"&D2:D5&"'!A2"),D1)>0,0))
Ctrl+Shift+Enter

or,

try this non-CSE formula :

=INDEX(D2:D5,MATCH(1,INDEX(COUNTIF(INDIRECT("'"&D2:D5&"'!A2"),D1),0),0))

Regards
Bosco


i emailed you a follow up question but i figured it out!! i had 3 different cell numbers that the Max was pulling data from. I used a range of G1:G250 in my case to cover all the cells in that range. it worked!! awesome!
 
Top