2 rows, highest No in row 1, then highest number in row 2 relating to that column, possible duplicat

[John]

Hi folks,

I have 2 rows of numbers,

top row 100 110 120 110 120
2nd row 6 5 5 6 6

i need to find the highest number i the top row, which is easily done with
=max(a1:e1)

that would go into a cell where i want the results displayed. I then need to
check the cells and see which one had the high number and then get the
number directly below it. eg i would want 120 in my first results cell and
then 6 in the 2nd results cell. As you can see i have 2 lots of 120 in row
1, but have 2 different numbers in row 2, and need the highest of the 2
possibles displayed. So Ultimately i want it to look like this.

row 1 | 100 | 110 | 120 | 110 | 120
row 2 | 6 | 5 | 5 | 6 | 6

best | 120 | 6

i think a hlookup may do the job but my grasp of working out the formulas is
as good as my selecting the right lotto numbers.
Thanks for any help

 

[Biff]

Hi!

Suppose you have your max formula in G1:

G1: =MAX(A1:E1)

To get the max of the max use this formula entered as an array using the key
combination of CTRL,SHIFT,ENTER:

=MAX(IF(A1:E1=G1,A2:E2))

Biff

 

[John]

Biff.

Thanks mate, works an absolute treat. Much appreciated.

John

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Biff.

Thanks mate, works an absolute treat. Much appreciated.

John