INDEX with more that one criteria

[LegsMan]

Please refer to the attached file.

In Row 1 I want the value in F column that corresponds with the largest number in that column.

I.E. A1 has a value of 2 because A3:A10 max is 49

My issue is with B1 and C1 where the columns have 2 Max equal values. This is identified in Row 2 which is a count of the Max number in that column.

In this case I would like the number returned that has the Max value in Column G which is the Sum of that Row.

Thanks!

[hemesh]

copy paste in A1 then hold control and shift together and then hit enter to make it array formula
and drag to left
=MAX((MAX(A3:A10)=A3:A10)*$F$3:$F$10)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[LegsMan]

hemesh

Thank you for your reply. At first glance the array you gave me works out well;
however when I test it by changing the values in G column it does not return the
correct number. It appears to only return the highest number in F column.

I require the number in F column based on the higher value in G column when there are two identical Max values in A column.

[hemesh]

in A1 try below array
=MAX((MAX(A3:A10)=A3:A10)*$G$3:$G$10)

and drag to right