Searching for Data Values in a Column - Formula Only

  • Thread starter Thread starter Mark D'Agosta
  • Start date Start date
M

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)
 
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.
 
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)
 
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 said:
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 said:
A possible basic formula would be:
Click to expand...
=MATCH(3,A1:A100,0)-MAX(IF((ROW(A1:A100)<MATCH(3,A1:A100,0))*(A1:A100=1),ROW
Click to expand...
 
Back
Top