Formula help return header from max value

Occasional Contributor



I would like to return the header from the highest value. In the example below with the right formula in sheet 1, cell b4 this would return Cherries. 


Screen Shot 2022-04-27 at 9.08.53 am.pngScreen Shot 2022-04-27 at 9.09.09 am.png



4 Replies

@cheeseontoast101 Perhaps the attached file contains a workable solution for your problem. I've used named ranges so that you don't have to worry about sheet names.

Screenshot 2022-04-27 at 08.38.14.png



As variant

=INDEX(headers, SUMPRODUCT( ( (group1=MAX(group1, group2))+(group2=MAX(group1, group2)) )*( COLUMN(group1) - COLUMN(startCell) + 1 ) ) )



I keep getting a N/A result in mine. Unfortunately it has to be between to sheets. I'm just not sure what the problem is.

@cheeseontoast101 Then you need to show a bit more on how you use the formula in real life. With regard to having to use different sheets, that's fine. But, I'd still recommend that you use Named Ranges.