Return cell referance of the first cell that equals a value?

[Enginerd]

I have a 3000 row table that has three columns: Time, Sensor1 and Sensor2.

I need to break this table up into sub tables. I would like each sub table
to contain the different blocks of when sensor 1 starts at value of 250 and
ends at 302.

I am thinking that there should be some kind of function that will return
the cell referance of the first cell in list of cells that matches a certian
criteria.

In my perfect world I would like to past in new raw data over the old raw
data on the worksheet and have the sheet automatically adjust.

 

[T. Valko]

I am thinking that there should be some kind of function

that will return the cell referance of the first cell in list
of cells that matches a certian criteria.

This will return the cell address of the first instance of 250 in the range
A2:A1000:

=ADDRESS(MATCH(250,A2:A1000,0)+ROW(A2)-1,COLUMN(A1),4)

 

[Jacob Skaria]

The below formula will look for the text "a" in column A and return the first
matching cell address..Is that what you are looking for.

=ADDRESS(MATCH("a",A:A,0),1)


If this post helps click Yes

 

[Enginerd]

Thanks! I never would have thought of combining those two formulas.

 

[Enginerd]

Thanks!! I like your implementation.

This will return the cell address of the first instance of 250 in the range
A2:A1000:

=ADDRESS(MATCH(250,A2:A1000,0)+ROW(A2)-1,COLUMN(A1),4)

--
Biff
Microsoft Excel MVP





.

 

[T. Valko]

You're welcome. Thanks for the feedback!