It only returns the row number for rows that have a row number less than the
first row with a 3 in it and the row also contains a 1.

then the max function picks out the highest numbered row fitting these two
conditions.

--
Regards,
Tom Ogilvy

Mark D'Agosta wrote in message
...
Tom,

Quick question about the part of your formula that reads:

... * (A1:A100=1) ...

I not quite sure how this is evaluated in an array formula. Range A1:A100
contains a random mix of 1, 2, and 3. Does this just check to see which
number (1, 2, or 3) is the first number in the range? Can you give a
brief
explanation?

Much appreciated.

Mark D'Agosta



"Tom Ogilvy" wrote in message
...
A possible basic formula would be:



=MATCH(3,A1:A100,0)-MAX(IF((ROW(A1:A100)<MATCH(3,A1:A100,0))*(A1:A100= 1),ROW
(A1:A100)))

entered with Ctrl+Shift+Enter rather than just enter since it is an
array
formula. This subtracts the closest row with 1 below the row with 3.
YOu
might want to add a -1 on the end if you want the intervening number of
rows
(you said columns, but I assume you meant rows).

this assumes that there is a row with 1 closer to row1 than the first
row
with 3. You would have to add additional checks if this is not the
case.

--
Regards,
Tom Ogilvy