On Apr 3, 10:39*pm, TechTrend wrote:
On Apr 3, 10:22*pm, joeu2004 wrote:



On Apr 3, 6:57 pm, TechTrend wrote:

I have in column A *descending dates as:
Mar 1/11
[....]
Jan 4/10
Dec 1/09
Nov 2/09
[....]
I need a formula, that given a month (as number, 12
for example in cell B1) and the year (2009 for example,
in cell B2) gives me the position of the matching date (16).

On Apr 3, 1:02*pm, Pete_UK wrote:

=MATCH(DATE(B2,B1,1),A:A,0)

I presume Pete means:

=MATCH(DATE(B2,B1,1),A:A,-1)

Note that not all dates in column A are on day 1. *The -1 MATCH option
finds the smallest date greater than equal to month/1/year.

That assumes that column has try numeric dates that are formatted as
Custom "mmm d/yy" without quotes.

Thanks Pete and Joeu

Indeed had to use -1 instead of 0.

Cheers

The formula =MATCH(DATE(B2,B1,1),A1:A100,-1) works very well except
when the date (date(b2,b1,1) does not exist in the range a1:a100. In
that case it gives me the position of the last row with data (A100).

Is there a way to modify the formula to get in those cases #N/A or a
text "Don't exist" ??

Cheers
Mika