Return Numeric Values Matching EXACT Date for Criteria

[Sam via OfficeKB.com]

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

 

[Domenic]

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!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thanks for reply and your assistance. Cell AB4 does just contain a one or
two digit number.

So, I'm using your second suggestion:

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

What does the +0 actually do?

Cell AB4 contains the two digit number 31 and the month in B22 is June. It
returns has #N/A. Should it have returned #VALUE as the 31 June does not
exist?

=INDEX(OFFSET(Data,0,0,,10),MATCH($AB$4&"/"&TEXT($B22,"mm/yy")+0,Date,0),
COLUMN(A:A))

Cheers,
Sam

 

[Domenic]

Hi Domenic,

Thanks for reply and your assistance. Cell AB4 does just contain a one or
two digit number.

So, I'm using your second suggestion:

What does the +0 actually do?

That part of the formula returns the date as a text string. The +0 bit
coerces it into a true date value.

Cell AB4 contains the two digit number 31 and the month in B22 is June. It
returns has #N/A. Should it have returned #VALUE as the 31 June does not
exist?

=INDEX(OFFSET(Data,0,0,,10),MATCH($AB$4&"/"&TEXT($B22,"mm/yy")+0,Date,0),
COLUMN(A:A))

Opening and closing brackets for the lookup value have been omitted.
The formula should be as follows...

=INDEX(OFFSET(Data,0,0,,10),MATCH(($AB$4&"/"&TEXT($B22,"mm/yy"))+0,Date,0
),COLUMN(A:A))

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you very much for explanation and corrected Formula. That's Great!

Cheers,
Sam

[quoted text clipped - 5 lines]

What does the +0 actually do?