Index(Max(if?

[Jamie Wagler]

Please refer to the attached file.

What I require is in A1 the value from F3:F10 column that corresponds with the MAX value in A3:A10. A1 should come out as 2 because 49 is the max in that column.

This is to continue B1 through E1

When there are 2 or more identical Max values in the same column such as in B and C) the higher corresponding value in G3:G10 column should act as the tie breaker.
B1 should be 6 because the 55 in G, C1 should be 5 because of the 65 in G

I have been trying the following

=INDEX($A$3:$G$10,IF(A2=1,MAX($A$3:$A$10),MAX($G$3:$G$10)),6)

But am getting back a #REF! error

 

[vletm]

@Jamie Wagler
#REF comes because Your row value is over 8 (49 or 65).
You could test next to cell[A1] ... ";" or ","
=INDEX($A3:$G10;MATCH(MAX(A3:A10);A3:A10;0);6)
or
=INDEX($A3:$G10;MATCH(MAX(A3:A10),A3:A10,0),6)

 

[shrivallabha]

Following seems to work. I think there could be elegant alternative. This is array formula so you have to commit by Control+Shift+Enter. In cell A1:
=INDEX($F$3:$F$10,MATCH(MAX(IF(A3:A10=MAX(A3:A10),1,0)*$G$3:$G$10),IF(A3:A10=MAX(A3:A10),1,0)*$G$3:$G$10,0))

 

[Asheesh]

One more--

MAX(IF((A$3:A$10=MAX(A$3:A$10))*$G$3:$G$10=MAX(IF(A$3:A$10=MAX(A$3:A$10),$G$3:$G$10)),$F$3:$F$10))

TO be array entered

 

[Asheesh]

Isn't it a duplicate thread
http://forum.chandoo.org/threads/vlookup-or-index.27591/#post-165300

 

[bines53]

Hi,

Another one, without cse

=MATCH(MAX(A3:A10),A3:A10,0)

david

 

[shrivallabha]

@Asheesh Not sure if it is complete duplicate but the file provided is the same but requirements are stated bit differently (MAX here and commonly appearing number).

@bines53 You have overlooked tie part.

 

[bines53]

Hi @ shrivallabha

OK,
He wants to get the last line, the number of repeats several times,

 

[John Jairo V]

Hi to all!

This could work:

=LOOKUP(2,1/(A3:A10+$G3:$G10/9^9=MAX(A3:A10+$G3:$G10/9^9)),$F3:$F10)
Or:
=LOOKUP(,0/FREQUENCY(0,1/(A3:A10+$G3:$G10/9^9)),$F3:$F10)

Blessings!

 

[shrivallabha]

Hi @ shrivallabha

OK,
He wants to get the last line, the number of repeats several times,

Not really. In case of tie, column G shall be referred for working out MAX for breaking tie.

 

[bines53]

Hello friends,

Here's my solution,

=MATCH(MAX(MMULT((A3:A10=MAX(A3:A10))*$G$3:$G$10,1)),MMULT((A3:A10=MAX(A3:A10))*$G$3:$G$10,1),0)

David

 

[bines53]

Another, shorter,

=MATCH(MAX(MMULT((A3:A10)*($G$3:$G$10),1)),MMULT((A3:A10)*($G$3:$G$10),1),0)

=AGGREGATE(14,6,ROW(A3:A10)-2/((A3:A10)*($G$3:$G$10)=MAX((A3:A10)*($G$3:$G$10))),1)

=MAX(MMULT(N((A3:A10)*($G$3:$G$10)=MAX((A3:A10)*($G$3:$G$10)))*ROW(A3:A10)-2,1))


David