Finding row of a date

[Mika]

Hi guys/gals


I have in column A a list of dates in descending order. Let=B4s say I
have in cell c1 a date, I need in cell D1 the row , in A, which has
that date and, in case it doesn=B4t exist, it should give the row of the
next following date below (closest "match" to date in c1).

I tried:
match(datenumber(TEXT(C1,"dd/mm/yy")),index(A1:A1813,,0),-1) that works
fine when the date exist but error if not.

Thanks in advance,
mika

[Peo Sjoblom]

One way




=MATCH(SMALL(A1:A813,COUNTIF(A1:A813,"<"&C1)+1),A1:A813,0)



if you want the cell address




=CELL("address",INDEX(A1:A813,MATCH(SMALL(A1:A813,COUNTIF(A1:A813,"<"&C1)+1),A1:A813,0)))


Btw, I am using the stupid web newsgroups at the moment and I sent this
answer 10 minutes ago so I apologize if it is posted twice


Regards,

Peo Sjoblom

"Mika" wrote:

> Hi guys/gals
>
>
> I have in column A a list of dates in descending order. Let´s say I
> have in cell c1 a date, I need in cell D1 the row , in A, which has
> that date and, in case it doesn´t exist, it should give the row of the
> next following date below (closest "match" to date in c1).
>
> I tried:
> match(datenumber(TEXT(C1,"dd/mm/yy")),index(A1:A1813,,0),-1) that works
> fine when the date exist but error if not.
>
> Thanks in advance,
> mika
>
>

[Mika]

Thanks Peo,

That was quick!. I only need the row, youre formula 1 works well when
the value exist.
It gives an error when the value does not exists and the answer is the
first element of the table A1.

When the value does not exist and is in any other position, it gives
the previous date but not the next.

I think I can now take it from here to fix it, at leats you already
know it !.
Thanks

[JB]

http://cjoint.com/?fuqZW2QRRi

=INDEX(range;Match(searchDate;range;-1))

Cordialy JB

[Mika]

Thanks JB,

I haven=B4t realized you answered my questions... though there is a
problem with your formula: if you search Dec 31 2006, it should give
the highest date in your attached file but it returns #N/A.

rgd
Mika

JB wrote:
> http://cjoint.com/?fuqZW2QRRi
>=20
> =3DINDEX(range;Match(searchDate;range;-1))
>=20
> Cordialy JB