Google Sheets – Display Sheet Name with Highest Value in Cell

google sheets

I have a sheet for each data set i have. I want to display in a cell the name of the sheet with the highest value in all the B2 cells.

Best Answer

Roughly stated, the below formula will find "the highest B2", count how many "B2s" it had to look in to then find that value, then count that same number into a list of sheet titles to print the right one. You'll have to fix the formula to hold your actual dataset sheet names, but at least the pattern is straightforward. That's also why it is broken up onto multiple lines—for ease of editing. Do keep an eye on those double-quotes, single-quotes, and …!B2 references if you try this approach.

There are other ways to accomplish your goal, but I'd guess none so accessible and maintainable.

=INDEX({
"Dataset 1","Dataset 2","Dataset 3"
},MATCH(MAX(
'Dataset 1'!B2,'Dataset 2'!B2,'Dataset 3'!B2
),{
'Dataset 1'!B2,'Dataset 2'!B2,'Dataset 3'!B2
},0))
Related Topic