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 <at>yahoo<dot>com
----
"Impakt" <[email protected]> wrote in message
news:[email protected]...
> 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.

On Sat, 12 Feb 2005 11:24:41 +0800, "Max" <[email protected]>
wrote:

>One way
>
>Assuming the source data is in Sheet1
>

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


Kind regards

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" <[email protected]> wrote in message
news:[email protected]...
> 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 <at>yahoo<dot>com
----
"Impakt" <[email protected]> wrote in message
news:[email protected]...
> On Sat, 12 Feb 2005 11:24:41 +0800, "Max" <[email protected]>
> 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.