position of second occurrance

[Stefi]

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

 

[Ron Rosenfeld]

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

 

[Stefi]

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

 

[Ron Rosenfeld]

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

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

 

[Stefi]

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


„Ron Rosenfeld†ezt írta:

 

[Stefi]

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

„Stefi†ezt írta:

 

[Ron Rosenfeld]

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