Formula for max number and the data adjacent to the cell
 

Hello,

I need a formula to look for a range of cells and display the max number
among them along with the data adjacent to the cell in a different cell.

Let me explain in this way..

A1 B1
ABC 231
KJH 122
UJJ 422
IOJ 321
FJK 545

I need a formula which looks the maximum number from B1 to B5 and get
displayed in B10 (=MAX(B1:B5) can be used)
but I want the data adjacent to the max number (FJK in this case) to get
displayed along with the max number in the cell A10.

How can I do that?

Thanks in advance
Sasikiran

Formula for max number and the data adjacent to the cell
 

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

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sasikiran" wrote in message
...
Hello,

I need a formula to look for a range of cells and display the max number
among them along with the data adjacent to the cell in a different cell.

Let me explain in this way..

A1 B1
ABC 231
KJH 122
UJJ 422
IOJ 321
FJK 545

I need a formula which looks the maximum number from B1 to B5 and get
displayed in B10 (=MAX(B1:B5) can be used)
but I want the data adjacent to the max number (FJK in this case) to get
displayed along with the max number in the cell A10.

How can I do that?

Thanks in advance
Sasikiran

Formula for max number and the data adjacent to the cell
 

B10: =MAX($B$2:$B$6)

A10: =INDEX($A$2:$A$6,MATCH($B$10,$B$2:$B$6,0))

Regards,
Stefi



€žSasikiran€� ezt Ã*rta:

Hello,

I need a formula to look for a range of cells and display the max number
among them along with the data adjacent to the cell in a different cell.

Let me explain in this way..

A1 B1
ABC 231
KJH 122
UJJ 422
IOJ 321
FJK 545

I need a formula which looks the maximum number from B1 to B5 and get
displayed in B10 (=MAX(B1:B5) can be used)
but I want the data adjacent to the max number (FJK in this case) to get
displayed along with the max number in the cell A10.

How can I do that?

Thanks in advance
Sasikiran

Formula for max number and the data adjacent to the cell
 

Thanks a lot Bob... Its working fine..

"Bob Phillips" wrote:

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

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sasikiran" wrote in message
...
Hello,

I need a formula to look for a range of cells and display the max number
among them along with the data adjacent to the cell in a different cell.

Let me explain in this way..

A1 B1
ABC 231
KJH 122
UJJ 422
IOJ 321
FJK 545

I need a formula which looks the maximum number from B1 to B5 and get
displayed in B10 (=MAX(B1:B5) can be used)
but I want the data adjacent to the max number (FJK in this case) to get
displayed along with the max number in the cell A10.

How can I do that?

Thanks in advance
Sasikiran