offset problem with finding last entry in a row

[Paul]

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)),2),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)),2),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

 

[Ashish Mathur]

Hi Paul,

Try this. Replace your offset function with -

OFFSET(EPC!$B$17,0,0,COUNTA(EPC!$B:$B)-1)

The -1 is for the heading of column B. if there are any more text values
then exclude those by reducing -1 further to -2 or whatever.

Hope this helps

Regards,

Ashish Mathur

 

[Frank Kabel]

Hi
just as alternative: why do you want to claculate this. Why not simply
use:
=INDEX(B$17:B$10000,MATCH(LARGE(M$17:M$10000,2),M$17:M$10000,0),1)

 

[Aladin Akyurek]

The range that you want to construct would be:

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

which you can use in other formulas. However, if there are multiple
instances of the second highest number in M, you'll get just the "address"
from B corresponding to the first instance.