find row for max of all columns in a Pivot table

[Rabia Malik]

Hi,

I have a pivot table with format as follows:

A B C D
1 company name country1 country2 country3 ...
2 company1 10 2 3
3 company2 1 35 5
4 company3 0 4 10

I find the highest export quantity in all countries by the formula max(B2:D4) which comes out as 35.

Now I want to find the company name corresponding to this max value.

Need urgent help with this please. I've spent several hours on just this question of my project.

Regards

 

[Khalid NGO]

Hello @Rabia Malik, welcome to the forum

There might be more simple ways, I came up with the following {array formula}:

=INDEX($A$2:$A$4,MATCH(TRUE,MMULT(--($B$2:$D$4=MAX($B$2:$B$4,$C$2:$C$4,$D$2:$D$4)),TRANSPOSE(--(COLUMN($B$2:$D$4))))>0,0))

{array formula needs to be entered with a key combination of
Ctrl+Shift+Enter, instead of just enter}

Regards,

 

[bosco_yip]

Or this (non-array formula),

=INDEX(A2:A4,SUMPRODUCT((MAX(B2:D4)=B2:D4)*ROW(1:3)))

Regards
Bosco

 

[Khalid NGO]

Or this (non-array formula),

=INDEX(A2:A4,SUMPRODUCT((MAX(B2:D4)=B2:D4)*ROW(1:3)))

Neat one Bosco
I was sure you or someone will bring much elegant way.