Does AB4 contain a true date value formatted to display the day, or does
it actually contain a one or two digit number. If the former, try
replacing...

DATE(YEAR($B22),MONTH($B22),DAY($AB$4))

with

(TEXT($AB$4,"d")&"/"&TEXT($B22,"mmm/yy"))+0

Otherwise, try...

($AB$4&"/"&TEXT($B22,"mmm/yy"))+0

Note that the formula will return #VALUE! when the date doesn't exist,
such as 31/06/2006, and will return #N/A when the date is not found. If
so desired, the formula can be amended to trap error values.

Hope this helps!

In article <680e888d7f189@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi All,

I would like a Formula that returns numeric values ONLY if they MATCH the
EXACT date.

The Formula below does work in part. But when the actual date does not exist
or a value for that date does not exist, the Formula returns a value nearest
that date. That is, 31/06/2006 does not exist as June only has 30 days. But
the Formula retruns a value that has a date 01/07/2006.

=INDEX(OFFSET(Data,0,0,,10),MATCH(DATE(YEAR($B22), MONTH($B22),DAY($AB$4)),
Date,0),COLUMN(A:A))

The dynamic named range "Data" spans 10 columns and many rows - houses
numeric values. I would like to have numeric values returned using an EXACT
date for the criteria. "Date" is a single column dynamic range. The values in
"Date" are formatted as 11/06/2006. The values in column "B" are also
formatted as 11/06/2006. The Day in column $AB$4 can be a single or double-
digit: 1, 11, 31 etc.


Thanks
Sam