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 :)

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

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" wrote in message
...
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 :)

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

Biff.

Thanks mate, works an absolute treat. Much appreciated.

John


"Biff" wrote in message
...
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" wrote in message
...
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 :)

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

You're welcome. Thanks for the feedback!

Biff

"John" wrote in message
...
Biff.

Thanks mate, works an absolute treat. Much appreciated.

John


"Biff" wrote in message
...
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" wrote in message
...
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 :)