Help pls! Max func to display value of different cell?
 

Hi all,

I am trying to use the max function to find the highest value in a
row, but display the value of another row (same column).

Eg: Find the highest value in the row A10:H10 but display the value
of row A5:H5 that matches the column the the highest value was found
in.

In other words...If D10 has the highest value (total rainfall for
April), and D5 is the label for the column (April) , I want to dsplay
the name of the month (D5) as the result, not the value of D10.

Can this be done? Or is there a better way?


Thanks

Paul.

One way

Assuming the source data is in Sheet1

In Sheet2
--------------
Try in say, B2:

=INDEX(Sheet1!$A$5:$H$5,MATCH(MAX(Sheet1!$A$10:$H$ 10),Sheet1!$A$10:$H$10,0))

B2 will return the value within Sheet1!$A$5:$H$5
which corresponds to the max value within Sheet1!$A$10:$H$10

(It's assumed there's no duplicate max values in Sheet1!$A$10:$H$10)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Impakt" wrote in message
...
Hi all,

I am trying to use the max function to find the highest value in a
row, but display the value of another row (same column).

Eg: Find the highest value in the row A10:H10 but display the value
of row A5:H5 that matches the column the the highest value was found
in.

In other words...If D10 has the highest value (total rainfall for
April), and D5 is the label for the column (April) , I want to dsplay
the name of the month (D5) as the result, not the value of D10.

Can this be done? Or is there a better way?


Thanks

Paul.

=INDEX(A5:H5,MATCH(MAX(A10:H10),A10:H10,0))

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Impakt" wrote in message
...
Hi all,

I am trying to use the max function to find the highest value in a
row, but display the value of another row (same column).

Eg: Find the highest value in the row A10:H10 but display the value
of row A5:H5 that matches the column the the highest value was found
in.

In other words...If D10 has the highest value (total rainfall for
April), and D5 is the label for the column (April) , I want to dsplay
the name of the month (D5) as the result, not the value of D10.

Can this be done? Or is there a better way?


Thanks

Paul.

You're welcome !
Thanks for posting back ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Impakt" wrote in message
...
On Sat, 12 Feb 2005 11:24:41 +0800, "Max"
wrote:

One way

Assuming the source data is in Sheet1


Thank you for that...I'll try it out.


Kind regards

Paul.

On Sat, 12 Feb 2005 11:24:41 +0800, "Max"
wrote:

One way

Assuming the source data is in Sheet1


Thank you for that...I'll try it out.


Kind regards

Paul.

Ok, so I should read about INDEX and MATCH.
I had no idea where to look in the book/help.

Thanks again guys (and to anyone else who may reply).

I'm sure you'll hear from me again as I do battle with my excel
studies!


Kind regards

Paul.