cell reference from =max
 

What i am trying to do is find the maximum value from a list of ammounts,
then from that maximum value (which i need in one cell as a value) i need to
find who that max figure relates to from a list of names ie.
Sheet2
A B
1 fred 1.00
2 gareth 2.00
3 laura 3.00
Sheet3
A B
1 =max('sheet2'!B1:B3) =3.00 this cell needs to display
name ie laura

god what a nightmare, also from this i need to do the same with
=large('sheet2'!B1:B2,2)

Please can someone help, thanks

=INDEX(A:A,MATCH(MAX(B:B ),B:B,0))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Garethm1976" wrote in message
...
What i am trying to do is find the maximum value from a list of ammounts,
then from that maximum value (which i need in one cell as a value) i need
to
find who that max figure relates to from a list of names ie.
Sheet2
A B
1 fred 1.00
2 gareth 2.00
3 laura 3.00
Sheet3
A
B
1 =max('sheet2'!B1:B3) =3.00 this cell needs to display
name ie laura

god what a nightmare, also from this i need to do the same with
=large('sheet2'!B1:B2,2)

Please can someone help, thanks

Try:

=INDEX(Sheet2!A:A,MATCH(MAX(Sheet2!B:B),Sheet2!B:B ,0))

for the max. For the 2nd largest:

=INDEX(Sheet2!A:A,MATCH(LARGE(Sheet2!B:B,2),Sheet2 !B:B,0))

HTH
Jason
Atlanta, GA


"Garethm1976" wrote:

What i am trying to do is find the maximum value from a list of ammounts,
then from that maximum value (which i need in one cell as a value) i need to
find who that max figure relates to from a list of names ie.
Sheet2
A B
1 fred 1.00
2 gareth 2.00
3 laura 3.00
Sheet3
A B
1 =max('sheet2'!B1:B3) =3.00 this cell needs to display
name ie laura

god what a nightmare, also from this i need to do the same with
=large('sheet2'!B1:B2,2)

Please can someone help, thanks

=INDEX(Sheet2!A1:A3,MATCH(MAX(Sheet2!B1:B3),Sheet2 !B1:B3,0),1)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Garethm1976" wrote in message
...
What i am trying to do is find the maximum value from a list of ammounts,
then from that maximum value (which i need in one cell as a value) i need
to
find who that max figure relates to from a list of names ie.
Sheet2
A B
1 fred 1.00
2 gareth 2.00
3 laura 3.00
Sheet3
A
B
1 =max('sheet2'!B1:B3) =3.00 this cell needs to display
name ie laura

god what a nightmare, also from this i need to do the same with
=large('sheet2'!B1:B2,2)

Please can someone help, thanks

Thanks,
It worked, thank you very very much.

Regards Gareth.

"Jason Morin" wrote:

Try:

=INDEX(Sheet2!A:A,MATCH(MAX(Sheet2!B:B),Sheet2!B:B ,0))

for the max. For the 2nd largest:

=INDEX(Sheet2!A:A,MATCH(LARGE(Sheet2!B:B,2),Sheet2 !B:B,0))

HTH
Jason
Atlanta, GA


"Garethm1976" wrote:

What i am trying to do is find the maximum value from a list of ammounts,
then from that maximum value (which i need in one cell as a value) i need to
find who that max figure relates to from a list of names ie.
Sheet2
A B
1 fred 1.00
2 gareth 2.00
3 laura 3.00
Sheet3
A B
1 =max('sheet2'!B1:B3) =3.00 this cell needs to display
name ie laura

god what a nightmare, also from this i need to do the same with
=large('sheet2'!B1:B2,2)

Please can someone help, thanks

Also of interest:

http://tinyurl.com/aft5l

for it looks like you want to extract a Top N (Top 2) list.

Garethm1976 wrote:
What i am trying to do is find the maximum value from a list of ammounts,
then from that maximum value (which i need in one cell as a value) i need to
find who that max figure relates to from a list of names ie.
Sheet2
A B
1 fred 1.00
2 gareth 2.00
3 laura 3.00
Sheet3
A B
1 =max('sheet2'!B1:B3) =3.00 this cell needs to display
name ie laura

god what a nightmare, also from this i need to do the same with
=large('sheet2'!B1:B2,2)

Please can someone help, thanks