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