Find row for max value of all columns in a pivot table

[rabiamalik]

Hi,

I have a pivot table 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

 

[XLearner]

Hi,

(replace ; with your usual separator (ie ,) where appropriate:

=INDEX($A$1:$A$4; SUMPRODUCT((B3:D4=MAX(B3:D4))*ROW(B3:D4)); 1)

Regards
XLearner

 

[XLearner]

Hi,

In case you would have 2 (or more) Companies with the same MAX value the following formula is more robust in the sense it will report the 1st one found instead of an error:

=INDEX($A$1:$A$4; SMALL(IF(B2:D4=MAX(B2:D4);ROW(B2:D4));1); 1)

validate it with [Ctrl]+[Shift]+[Enter] as this is an array formula

AND in the previous proposal it's not ((B3:D4=MAX(B3:D4))*ROW(B3:D4)) but ((B2:D4=MAX(B2:D4))*ROW(B2:D4)) - Sorry

Regards
XLearner