Searching for Data Values in a Column - Formula Only

[Mark D'Agosta]

All,

Column A will contain a variable number of rows that contain values from 1
to 4. I'd like to create a formula in cell B1 that searches column A from
top to bottom for the first occurrence of 3, then searches backwards
(towards row #1) for the first prior instance of 1, then writes out the
number of columns between the two cells.

I got as far as finding the first occurrence of 3 using this formula
"=MATCH(3,A1:A100,0)", but that's as far as I got.

Any help will be greatly appreciated.

Mark D'Agosta
(e-mail address removed)

 

[Tom Ogilvy]

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.

 

[Mark D'Agosta]

Tom,

That did the trick! Thanks a million.

Mark

 

[Mark D'Agosta]

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
(e-mail address removed)

 

[Tom Ogilvy]

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

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
(e-mail address removed)

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