position of second occurrance

  • Thread starter Thread starter Stefi
  • Start date Start date
S

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
 
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
Click to expand...

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).
Click to expand...
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
 
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
Click to expand...

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:
 
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 Ron again, now I understand the logic of the formula, it's not very
complicated, but really tricky.
Regards,
Stefi
Click to expand...

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
 
Back
Top