Hi All,

I have a range (C11:AF11) with numbers in it, e.g. (C11:N11, the rest of the
range is empty):

-1100 -900 -810 -800 -850 -1000 -700 -810
I'd like to determine the position of the last entry meeting criterium
<-700, in my example it is 9 (the position of the second -810). I tried

=MAX(MATCH(HLOOKUP(-700,C11:AF11,1),C11:AF11,0))
as an array formula but it still returns 4 (the position of the first -810).

Please help!

Thanks,
Stefi

On Tue, 24 Jun 2008 02:48:02 -0700, Stefi
wrote:

Hi All,

I have a range (C11:AF11) with numbers in it, e.g. (C11:N11, the rest of the
range is empty):

-1100 -900 -810 -800 -850 -1000 -700 -810
I'd like to determine the position of the last entry meeting criterium
<-700, in my example it is 9 (the position of the second -810). I tried

=MAX(MATCH(HLOOKUP(-700,C11:AF11,1),C11:AF11,0))
as an array formula but it still returns 4 (the position of the first -810).

Please help!

Thanks,
Stefi

I have no idea how you get a value of 9 for the second -810 (or a value of 4
for the first -810).

To get the number of the column in which your last value meeting your criteria
exists, try this **array-entered** formula.

=MAX((LOOKUP(2,1/(C11:AF11<-700),C11:AF11)=C11:AF11)*COLUMN(C11:AF11))

To **array** enter a formula, after pasting the formula into the formula bar,
hold down <ctrl<shift while hitting <enter. If you do this correctly, Excel
will place braces {...} around the formula.

With your data, the above returns a value of 10, since the last -810 is in
column J which is the 10th column.

To get a 9, you would subtract 1 (or subtract Column(B11) ) or do whatever
manipulation you need to do to come up with the value you want.

--ron

Thanks Ron, it works, although I need some time to analyse and understand it.

I have no idea how you get a value of 9 for the second -810 (or a value of 4
for the first -810).
You are right, my values were really
C D E F...
-1100 blank -900 -810 -800 -850 -1000 -700 -810

I made an error when copying them into the post.

Regards,
Stefi

On Tue, 24 Jun 2008 04:36:00 -0700, Stefi
wrote:

Thanks Ron, it works, although I need some time to analyse and understand it.

I have no idea how you get a value of 9 for the second -810 (or a value of 4
for the first -810).
You are right, my values were really
C D E F...
-1100 blank -900 -810 -800 -850 -1000 -700 -810

I made an error when copying them into the post.

Regards,
Stefi

Glad to help. Thanks for the feedback.

If you use the Evaluate Formula tool, you will likely see how it works. If
not, post back and we'll go through it step by step.
--ron

Thanks for the tip, I'LL try it and post back the result.
Stefi


€žRon Rosenfeld€� ezt Ã*rta:

On Tue, 24 Jun 2008 04:36:00 -0700, Stefi
wrote:

Thanks Ron, it works, although I need some time to analyse and understand it.

I have no idea how you get a value of 9 for the second -810 (or a value of 4
for the first -810).
You are right, my values were really
C D E F...
-1100 blank -900 -810 -800 -850 -1000 -700 -810

I made an error when copying them into the post.

Regards,
Stefi

Glad to help. Thanks for the feedback.

If you use the Evaluate Formula tool, you will likely see how it works. If
not, post back and we'll go through it step by step.
--ron

Thanks Ron again, now I understand the logic of the formula, it's not very
complicated, but really tricky.
Regards,
Stefi

€žStefi€� ezt Ã*rta:

Thanks for the tip, I'LL try it and post back the result.
Stefi


€žRon Rosenfeld€� ezt Ã*rta:

On Tue, 24 Jun 2008 04:36:00 -0700, Stefi
wrote:

Thanks Ron, it works, although I need some time to analyse and understand it.

I have no idea how you get a value of 9 for the second -810 (or a value of 4
for the first -810).
You are right, my values were really
C D E F...
-1100 blank -900 -810 -800 -850 -1000 -700 -810

I made an error when copying them into the post.

Regards,
Stefi

Glad to help. Thanks for the feedback.

If you use the Evaluate Formula tool, you will likely see how it works. If
not, post back and we'll go through it step by step.
--ron

On Tue, 24 Jun 2008 06:41:00 -0700, Stefi
wrote:

Thanks Ron again, now I understand the logic of the formula, it's not very
complicated, but really tricky.
Regards,
Stefi

It's good that you figured it out -- now you can apply that logic in other
areas.

Best wishes,


"Give a man a fish, and he'll eat for a day. Teach him to use the Internet,
and he won't bother you for months!"
--ron