FIND A VALUE RELATIVE TO THE POSITION OF ANOTHER VALUE

[Tinny426]

Hi all, Got a quandary in my Excel spreadsheet. See simplified example attached for reference.

I need to find the highest number of a particular piece of fruit in all 3 worksheets and then the name of the person in that particular row. The number must be found first and then the name.

I have worked out how to get the highest number of each piece of fruit, but i can't find out how to get the name.

For example, The maximum number of apples is 54 (MARCH, CELL D2), and the name is Richard (MARCH, CELL A2). 

How do i get the name Richard to go in RESULTS, CELL C2 ?

I hope is have explained myself adequately. Thanks in advance. Richard.

[#2]

@Tinny426 

Personally I'd add in a clause to locate the column containing each fruit in each of the sheets, though you seem to be happy manually typing in those ranges.

In C2:

=LET(ζ,CHOOSE({1,2},
   TOCOL(CHOOSE({1,2,3},JANUARY!B2:B5,FEBRUARY!E2:E5,MARCH!D2:D5),,1),
   TOCOL(JANUARY:MARCH!A2:A5)),
   VLOOKUP(B2,ζ,2,0)
)

The part JANUARY:MARCH!A2:A5 is a 3D reference and assumes that JANUARY and MARCH will always be the leftmost and rightmost respectively of the worksheets being interrogated.

If you're not using an English-language version of Excel, then the parts {1,2} and {1,2,3} may not work for you. These can easily be replaced with non-locale-dependent equivalents if so.

Regards

[#3]

Hi Jos, Your formula works perfectly in the example i made, but not in my actual spreadsheet, due the differences i couldnt cover in the example. But i have sorted my issue out thanks to your help.

Cheers Richard.