Get part of field name for highest value

[Lorna_Jane]

Hello, I have a spreadsheet which looks like the following:
AF AG AH
PercentRegion1 PercentRegion2 PercentRegion3 <-- Column header
100 0 0
25 75 0
1 0 99

I would like to add another field which would be called "dominant region"
and would be populated with the region number of the highest percentage i.e.
1
2
3

Any help is appreciated

 

[Ron Rosenfeld]

Hello, I have a spreadsheet which looks like the following:
AF AG AH
PercentRegion1 PercentRegion2 PercentRegion3 <-- Column header
100 0 0
25 75 0
1 0 99

I would like to add another field which would be called "dominant region"
and would be populated with the region number of the highest percentage i.e.
1
2
3

Any help is appreciated

Since your regions are listed in order:

=MATCH(MAX(AF2:AH2),AF2:AH2,0)

If, on the other hand, you want everything after "region" in the column header,
then:

=MID(INDEX($AF$1:$AH$1,1,MATCH(MAX(AF2:AH2),AF2:AH2,0)),
SEARCH("region",INDEX($AF$1:$AH$1,1,MATCH(MAX(AF2:AH2),AF2:AH2,0)))+6,99)

--ron

 

[Luke M]

In case of a tie for highest value, this formula will favor the lower number
(if 1 and 3 are tied, formula returns 1).

=MATCH(AF2:AH2,MAX(AF2:AH2),0)