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

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.

-- 
Biff
Microsoft Excel MVP


"Dianne" <Dianne@discussions.microsoft.com> wrote in message 
news:0A0B123C-7E07-4780-8F75-55E1F8FD774E@microsoft.com...
>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 

=MATCH(DATEVALUE("9/16/2006"),A18:A32)+17
-- 
Gary''s Student - gsnu200909

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 (MVP - Excel)


"Dianne" <Dianne@discussions.microsoft.com> wrote in message 
news:0A0B123C-7E07-4780-8F75-55E1F8FD774E@microsoft.com...
>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 

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.

-- 
Rick (MVP - Excel)


"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
news:OYIpg1HbKHA.1652@TK2MSFTNGP05.phx.gbl...
> 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)...
>
> c
>
> ** Commit the formula using Ctrl+Shift+Enter, not just enter by itself.
>
> -- 
> Rick (MVP - Excel)
>
>
> "Dianne" <Dianne@discussions.microsoft.com> wrote in message 
> news:0A0B123C-7E07-4780-8F75-55E1F8FD774E@microsoft.com...
>>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
>