Index/Match finding a true date

[jeffreybrown]

Can't seem to figure out the magic key here.

In A1:B1 there are months in the form of Jan, Feb, etc. The formula below works fine and returns the value from A2:B2

Please Login or Register  to view this content.

Change A1:B1 to a true date and this does not work

Please Login or Register  to view this content.

If I make the date as text 'Jan 11 this will work

Please Login or Register  to view this content.

but how can I get it to work with a true date. It seems it may have something to do with adding +0 but no luck so far.

Ideas on what I am missing?

[NBVC]

convert the date with +0

=INDEX($A$2:$B$2,MATCH(TEXT(TODAY(),"mmm-yy")+0,$A$1:$B$1,0))

Did you try that one?

[daddylonglegs]

If A1 and B1 are 1st of the month dates you can use this formula

=INDEX($A$2:$B$2,MATCH(TODAY(),$A$1:$B$1))

or

=LOOKUP(TODAY(),$A$1:$B$1,$A$2:$B$2)

although by using that version you haven't got an upper limit so if TODAY() is in March 2011 it will still match with the latest date (Feb)

You could also use an "array formula" like this

=INDEX($A$2:$B$2,MATCH(TEXT(TODAY(),"mmm-yy"),TEXT($A$1:$B$1,"mmm-yy"),0))

although I wouldn't really recommend it.....

or perhaps best

=INDEX($A$2:$B$2,MATCH(TODAY()-DAY(TODAY())+1,$A$1:$B$1,0))

assuming, again, that A1 and b1 are 1st of the month

[TMS]

Not sure what exactly you are trying to do, but "months in the form of Jan, Feb, etc" are text, hence , extracting the month from Today() as text will allow a match.

A true date is a number representing the number of days since 01/01/1900. That would be the reason you're not able to match. Match text with text, date with date.

Regards

[jeffreybrown]

Thanks NBVC/DLL,

@NBVC,

Yes I did try the formula you suggested, but it just returns #N/A. The date in A1:B1 is entered as 1/1/2011.

=INDEX($A$2:$B$2,MATCH(TEXT(TODAY(),"mmm-yy")+0,$A$1:$B$1,0))

@DLL,

It seems as though the last formula is the one to go with; however, just a question about the first one.

=INDEX($A$2:$B$2,MATCH(TODAY(),$A$1:$B$1))

If Today is 1/4/2011 then how come it will find the match with 1/1/2011? Does the formula fall back to the closet match?

Edit: I think I just figured it out maybe. Does it have to do with the third argument of the match function [match type]?

In NBVC's suggested formula I removed the 0 at the end and it works. Is this right?

[daddylonglegs]

NBVC's solution works for me. It's just another way to get 1st of the current month, I used

=TODAY()-DAY(TODAY())

whereas NBVC used

=TEXT(TODAY(),"mmm-yy")+0

In those versions you convert TODAY to 1st of the current month and then MATCH using zero as 3rd argument of MATCH function.....because you expect an exact match. However, with

=INDEX($A$2:$B$2,MATCH(TODAY(),$A$1:$B$1))

there is no 3rd argument (so it defaults to 1), perhaps I should use explicitly

=INDEX($A$2:$B$2,MATCH(TODAY(),$A$1:$B$1,1))

then TODAY() matches with the largest date in A1:B1 that is less than or equal to TODAY(), i.e. assuming A1:B1 are 1st of month dates it will match with the current month.

LOOKUP does the same. In both cases A1:B1 must be in ascending order which is what you'd expect for successive month dates anyway.....

[jeffreybrown]

Hi DLL,

Thanks for the further explanation. For whatever reason I still can't get it to work.

=INDEX($A$2:$H$2,MATCH(TEXT(TODAY(),"mmm-yy")+0,$A$1:$H$1,0))

When you get a change can you look at this workbook and tell me why this might be?

[NBVC]

I think it is because of the US vs. UK dating system...

If you use this:

=INDEX($A$2:$H$2,MATCH(TEXT(TODAY(),"mmm-yyyy")+0,$A$1:$H$1,0))

with four y's, then it should work in both countries....

with 2 y's the US sees it as January, the 11th, not January 2011.

[jeffreybrown]

Thanks NBVC...that's it. I could have sworn I tried yyyy, but I guess not. Thanks to both of you for all of your time.