Forum Discussion
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 ...
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
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.
Cheers Richard.