matching month and year column.

[Guest]

I need to use something like the below formula to match cell content from
the date column of one tab (B30 = 8-Dec-06), to the date column of another
tab (Dec-06). Notice one contains the day, the other only contains
month-year.
Obviously the below does not work. Is there something that will?

TIA,
Earl

=LOOKUP(month(B30),month('Satchmo Storage'!$B$6:S$6),'Satchmo
Storage'!$B$16:S$16)

 

[Peo Sjoblom]

I think you would need to give more info, Excel cannot use numeric
month-year dates so either it is a real date (select a cell and see what it
says in the formula bar) or text. You could use a combination of INDEX and
MATCH to solve this, if the values in B6:S6 are text you could use a HLOOKUP
as well.
First, use this formula

=ISTEXT(B6)

if we assume that all values in B6:S6 are alike then that formula should
return TRUE if the values are text.
If it returns FALSE check what the dates really are, if you are using US
regional settings and type in Dec-06
you'll probably get the date of 12/06/06 so check those dates first and post
back


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[Roger Govier]

Hi Earl

If your value of Dec-06 is a true Excel date, but formatted to show only
mmm-yy, then you could use
TEXT(B30,"mmm-yy") as your lookup.

 

[Peo Sjoblom]

Try


=INDEX('Satchmo Storage'!B16:S16,MATCH(MONTH('Dashboard
Week'!B32)&YEAR('Dashboard Week'!B32),MONTH('Satchmo
Storage'!B6:S6)&YEAR('Satchmo Storage'!B6:S6),0))

or

=INDEX('Satchmo Storage'!B16:S16,MATCH(1,('Satchmo
Storage'!B6:S6>=DATE(YEAR('Dashboard Week'!B32),MONTH('Dashboard
Week'!B32),1))*('Satchmo Storage'!B6:S6<=DATE(YEAR('Dashboard
Week'!B32),MONTH('Dashboard Week'!B32)+1,0)),0))




both entered with ctrl shift & enter




Regards,

Peo Sjoblom