Row Number

[Dianne]

I need hellp with a formula.

In Cell E8 I need to write a formula that will search Column A rows 18 thru
32 and if it finds the date 09/16/2006 it will display the row number.

Any help will be greatly appreciated.
Thanks
Dianne

 

[T. Valko]

You didn't say what result you want if the date is not present.

One way...

A1 = some date

=IF(COUNTIF(A18:A32,A1),INDEX(ROW(A18:A32),MATCH(A1,A18:A32,0)),"")

If the date isn't present the formula returns blank.

 

[Gary''s Student]

=MATCH(DATEVALUE("9/16/2006"),A18:A32)+17

 

[Rick Rothstein]

One more, an array entered** formula (it reports the row number of the last
matching cell in the range if there are more than one match and if reports 0
if there are no matches)...

=MAX(ROW(A18:A32)*(A18:A32=--"9/16/2009"))

** Commit the formula using Ctrl+Shift+Enter, not just enter by itself.

 

[Rick Rothstein]

I used the wrong year (from what you posted) in the date in my formula; the
correction is simple...

=MAX(ROW(A18:A32)*(A18:A32=--"9/16/2006"))

Note: Commit the formula using Ctrl+Shift+Enter, not just enter by itself.