Forum Discussion

Tinny426's avatar
Tinny426
Copper Contributor
Mar 16, 2023

FIND A VALUE RELATIVE TO THE POSITION OF ANOTHER VALUE

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.

Formulas and Functions

2 Replies

  • JosWoolley's avatar
    JosWoolley
    Iron Contributor
    Mar 16, 2023

    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

    • Tinny426's avatar
      Tinny426
      Copper Contributor
      Mar 19, 2023
      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.

Resources