how do i get field name from result of MAX or LARGE function

[Nick]

This is probably easy one but I cant figure it out.
I want to get the field name/label from the same row in the column to the
left of the result of a MAX or LARGE function. Tried LOOKUP but data is not
in order.

e.g.

A B
ReasonA 23
ReasonB 44
ReasonC 17 I would like MAX function to return 'Reason B'

[Trevor Shuttleworth]

=INDEX(A:A,MATCH(MAX(B:B),B1:B65534))

Regards

Trevor


"Nick" <[email protected]> wrote in message
news:[email protected]...
> This is probably easy one but I cant figure it out.
> I want to get the field name/label from the same row in the column to the
> left of the result of a MAX or LARGE function. Tried LOOKUP but data is
> not
> in order.
>
> e.g.
>
> A B
> ReasonA 23
> ReasonB 44
> ReasonC 17 I would like MAX function to return 'Reason B'

[RagDyeR]

Try this:

=INDEX(A1:A100,MATCH(MAX(B1:B100),B1:B100,0))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"Nick" <[email protected]> wrote in message
news:[email protected]...
This is probably easy one but I cant figure it out.
I want to get the field name/label from the same row in the column to the
left of the result of a MAX or LARGE function. Tried LOOKUP but data is not
in order.

e.g.

A B
ReasonA 23
ReasonB 44
ReasonC 17 I would like MAX function to return 'Reason B'

[Nick]

Thanks so much, it worked great. The ,0)) at the end makes all the
differance, but why?

"RagDyeR" wrote:

> Try this:
>
> =INDEX(A1:A100,MATCH(MAX(B1:B100),B1:B100,0))
>
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
> "Nick" <[email protected]> wrote in message
> news:[email protected]...
> This is probably easy one but I cant figure it out.
> I want to get the field name/label from the same row in the column to the
> left of the result of a MAX or LARGE function. Tried LOOKUP but data is not
> in order.
>
> e.g.
>
> A B
> ReasonA 23
> ReasonB 44
> ReasonC 17 I would like MAX function to return 'Reason B'
>
>
>

[Ragdyer]

The 0 at the end makes the Match() function look for exact matches *only*.
When looking for exact matches, the lookup data table does *not* have to be
sorted, and since yours (Column B) is not sorted, it returned the correct
value.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Nick" <[email protected]> wrote in message
news:[email protected]...
> Thanks so much, it worked great. The ,0)) at the end makes all the
> differance, but why?
>
> "RagDyeR" wrote:
>
> > Try this:
> >
> > =INDEX(A1:A100,MATCH(MAX(B1:B100),B1:B100,0))
> >
> > --
> >
> > HTH,
> >
> > RD
> > =====================================================
> > Please keep all correspondence within the Group, so all may benefit!
> > =====================================================
> >
> > "Nick" <[email protected]> wrote in message
> > news:[email protected]...
> > This is probably easy one but I cant figure it out.
> > I want to get the field name/label from the same row in the column to
the
> > left of the result of a MAX or LARGE function. Tried LOOKUP but data is
not
> > in order.
> >
> > e.g.
> >
> > A B
> > ReasonA 23
> > ReasonB 44
> > ReasonC 17 I would like MAX function to return 'Reason B'
> >
> >
> >