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

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

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" <lowj@bigpond.net.au> wrote in message
news:OoOCg.10190$rP1.2531@news-server.bigpond.net.au...
> 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.

Thanks mate, works an absolute treat. Much appreciated.

John


"Biff" <biffinpitt@comcast.net> wrote in message
news:uWFQnOOvGHA.5056@TK2MSFTNGP06.phx.gbl...
> 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" <lowj@bigpond.net.au> wrote in message
> news:OoOCg.10190$rP1.2531@news-server.bigpond.net.au...
>> 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
>>
>
>

You're welcome. Thanks for the feedback!

Biff

"John" <lowj@bigpond.net.au> wrote in message
news:gXRCg.10323$rP1.10255@news-server.bigpond.net.au...
> Biff.
>
> Thanks mate, works an absolute treat. Much appreciated.
>
> John
>
>
> "Biff" <biffinpitt@comcast.net> wrote in message
> news:uWFQnOOvGHA.5056@TK2MSFTNGP06.phx.gbl...
>> 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" <lowj@bigpond.net.au> wrote in message
>> news:OoOCg.10190$rP1.2531@news-server.bigpond.net.au...
>>> 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
>>>
>>
>>
>
>