Formula that works on individual cells return absurd value when run on merged cells

[Alexander Bjerkvik]

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 PROBLEM is 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%")

[#2 · Accepted solution]

Hi Alexander,

Merged cells is always an issue. I guess here is not with INDEX formula.

Your merged J81.. cell looks like

and if unmerge it it has a lot of side formulas inside

(by the way, here is your current max %).  If remove all junk formulas and keep only one

and now merge cells back (it will look as on first screenshot) you have correct result. At least here, perhaps in other places you have more side effects.

[#3]

Thanks allot! :D I had no idea there were so many junk formulas in my merged cells. I cleaned up, and it is correct now.