Formula help return header from max value

[cheeseontoast101]

Hi

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. 

[#2]

@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.

[#3]

@cheeseontoast101 

As variant

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

for

[#4]

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.

[#5]

@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.