find highest number display the complete row

[Paul]

Hi all I am new to this programming with Excel but I am trying to find a way
to look through a range of cells to find the highest number.

I want to display the neighboring cells to that highest number on another pl
ace, like on the top of my page. So I got a setup like this, and I am lookin
g for the highest number in column C:

A B C
text1 number1 10
text2 number2 98
text3 number3 85

I want some formula or something so that on the top of the page, so say abov
e text1, I can display the line

text2 number2 98

either in three different cells or (preferably even) in one cell

This is so that I can display the maximum (98) on the top of the page, which
is divided in two split screens.

Hope I explained the problem well enough and I would be happy with any sugge
stion.

Thanks in advance!
Paul

 

[Myrna Larson]

=INDEX($A:$A,MATCH(MAX($C:$C),$C:$C,0))&" "&
INDEX($B:$B,MATCH(MAX($C:$C),$C:$C,0))&" "&MAX($C:$C)

all on one line in the formula bar, of course

 

[Aladin Akyurek]

This kind of questions, more often than not, tend to overlook the fact that
there can be multiple instances of the Max value in the data. That is, what
if:

text1 number1 10
text2 number2 98
text3 number3 98

 

[Paul]

Thank you so much for your help. It worked like a charm for me. Now my
next problem is going to be a little more complicated. I have the
following formula that looks for the second highest number in column
M, and displays the corresponding TEXT in column B:

=INDEX(B$17:B$1936,MATCH(LARGE(M$17:M$1936,2),M$17:M$1936,0),1)

Now I would like to replace the range where Excel looks with the
selection where data is. IE, instead of B17:B1936 I want B17:B136 if
the last cell that contains data is B136.

I have to say that if there is an entry in column B there will also be
an entry in column M. And like I said, B consists of text and M of
numbers.

Obviously I have to do something with the offset function. I tried the
following which still gives the correct result:

=INDEX($B17:$B2000,MATCH(LARGE(OFFSET(EPC!$M$17,,,COUNTA(EPC!$M:$M)),5),OFFSET(EPC!$M$17,,,COUNTA(EPC!$M:$M)),0),1)

This still contains B17:B2000 though. If I change that to the
following then I get no result:

=INDEX((OFFSET(EPC!$B$17,,,COUNTA(EPC!$B:$B))),MATCH(LARGE(OFFSET(EPC!$M$17,,,COUNTA(EPC!$M:$M)),5),OFFSET(EPC!$M$17,,,COUNTA(EPC!$M:$M)),0),1)

What am I doing wrong?
Is there an easier solution??

Thanks in advance for your help.
Paul