One crack ..

Try this, array-enter the formula by pressing CTRL+SHIFT+ENTER instead of
just pressing ENTER:
=INDEX(J34:J500,MATCH(1,(ISNUMBER(S34:S500))*(S34: S5000)*((I34:I500="A")+(I34:I500="B"))*(N34:N500 =2),0)-1)
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AG" wrote in message
...
I would like a formula to find the value of a cell 1 row before certain
conditions are met.

Beginning with data in row 34 down I want to know the value of the cell in
column J 1 row before the row when these conditions are met for the 1st
time:
a. at the 1st occurrence of the value of the cell in column S being 0
b. the count of items in column N is = 2
c. the value of column I is either A or B

To explain by example, assume the 1st occurrence of a number 0 in column
S
occurs in row 100.
Prior entries in the range S34:S99 are entered by the text "NA" by design.
The formula would evaluate whether the count of items within the range
N34:N100 is = 0 and also determine if the value of cell I 100 has either
of
the text entries "A" or "B" by design.
If so the formula would have as its result the value of cell J99.

I hope I have explained this clearly enough for both a consideration and a
reply.
Thank you in advance.