I have developed this formula that enables me to match the highest number in a column with name of that product from another column, and add some text.
=INDEX(A81:A104;MATCH(MAX(M81:M104);M81:M104;0))&" is highest performing YTD at "&TEXT(MAX(M81:M104);"0,00%")
In my sheet "Best & Worst", that formula would write "Rare-earth is highest performing YTD at 70,25 %" in B82.
THE PROBLEMis that I get crazy numbers in B73 (Sheet: Best & Worst) when running this formula on merged cell categories (Cat. YTD). You find "Cat. YTD" on sheet "Case-by-Case" column J. You see that 2307,82 % YTD for Energy (in B73) is absurd, since Energy gets 15,51 % YTD in J81 (Sheet: Case-by-Case". However, the formula works in the other instances on sheet "Best & Worst".
I initially thought the problem is that I cant use colon (B81:B104) on merged cells. Hence, I tried to choose the merged category cells individually, and separate with semicolon. Here is the formula I have so far, but it does not work.
=INDEX((B81;B87;B94;B97;B99);MATCH(MAX(J81;J87;J94;J97;J99);(J81;J87;J94;J97;J99);0))&" is highest performing category YTD at "&TEXT(MAX(J81;J87;J94;J97;J99);"0,00%")