Excel INDEX MATCH MATCH on column title containing month and year? 1

[knifey]

Hi,
I have a master worksheet matrix consisting of lookup values in column H and months in the format 'Apr-13', 'May-13', etc. in cells AF1:BE1
What I need to do is pull the 'actuals' sales figures in to the master worksheet. The actuals worksheet is in exactly the same format as the master sheet.

I know how to use INDEX MATCH MATCH, but I've never tried it with dates. The following formula is working for the INDEX and row MATCH parts. But I can't get it to return anything but a #N/A error for the column MATCH part.
I need the column MATCH to work with 2 criteria (month and year). Can anyone point out where I'm going wrong?

=INDEX(Actuals!A1:BR2617,MATCH(Master!H2,Actuals!H:H,0),MATCH(MONTH(Master!AF1)&YEAR(Master!AF1),MONTH(Actuals!U1:BR1)&YEAR(Actuals!U1:BR1)),0)

Thanks,
K

 

[SkipVought]

hi,

but I've never tried it with dates...

Well is 'Apr-13' actually 1) a DATE (ie a NUMBER) or 2) TEXT?

The way that you determine the truth, is to change the number format for the cell containing 'Apr-13' to GENERAL.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Well from reading your post more carefully, it seems that your 'Apr 13' dates are ACTUAL DATES, formatted to display mmm yy.

So, just use the DATES, but use the [highlight #FCE94F]LESS THAN or EQUAL TO[/highlight] Match Type
[tt]
=INDEX(Actuals!A1:BR2617,MATCH( Master!H2,Actuals!H:H,0),MATCH([highlight #FCE94F]Master!AF1,Actuals!U1:BR1,1[/highlight]))
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[knifey]

Hi Skip,

I've tried changing the match type to 1 and this is now returning a result. Unfortunately it's the wrong result!
I have attached the file so you can get a better idea of what I'm trying to do. You can see that column BO of the master sheet (Mar-15) is matching to column AL of the actuals sheet (Jul-13). But I'm stumped as to why this is happening. Any ideas?

[URL unfurl="true"]https://drive.google.com/file/d/0Bx5r9tIItS8VZVpEX1NHVzlyT00/edit?usp=sharing[/url]

Thanks,
K

 

[SkipVought]

I really don't have time to analyze and debug your workbook.

RIGHT OFF, I looked at your dates in row 1 on MASTER and I see a problem, unless you intend the date in each column to be centered on the [highlight #FCE94F]tenth day of the month[/highlight]!!! So MASTER and ACTULAS dates are DIFFERENT!!!

Your formula UGH!!!
[tt]
AF1: =DATE(YEAR(CurrentMonth),MONTH(CurrentMonth)-[highlight #EF2929]24[/highlight],DAY(CurrentMonth))
[/tt]
and it turns out that CurrentMonth = 4/[highlight #FCE94F]10[/highlight]/2014. And each formula in row 1 is [highlight #EF2929]DIFFERENT[/highlight]!

So if you want the first date to be 24 month earlier than the CurrentMonth, then 1) make a NamedRange (I'm calling it StartDate), like hat contains THAT date...
[tt]
=DATE(YEAR(StartDate),MONTH(StartDate),DAY(StartDate))
[/tt]
Then next formula to COPY ACROSS...
[tt]
AG1: =DATE(YEAR(AF1),MONTH(AF1)+1,DAY(AF1))
[/tt]

So bottom line: the DATES in the summary sheets ought to be the FIRST day of the month (calendar or accounting)

On the incorrect results, your INDEX is referencing from column A ratherr than column U!

I woud STRONGLY urge you to use Named Ranges for EVERYTHING, rather than range references. It would greatly help 1) understanding how your formulas workb and 2) maintanance.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[knifey]

Thanks Skip,
Working perfectly, you've saved my bacon again. I've made all the changes, added named ranges and the formula now looks like this:
=INDEX(ActualsDataRange,MATCH(Master!$H2,ActualsMAMapName,0),MATCH(Master!AF$1,ActualsMonths,0))

Thanks again,
K

 

[SkipVought]

[tt]
=INDEX(ActualsDataRange,MATCH(Master!$H2,ActualsMAMapName,0),MATCH(Master!AF$1,ActualsMonths,[highlight #F57900] 1[/highlight] ))
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[knifey]

Hi Skip,
Match type 1 is giving exactly the same results as 0. What is the difference? Surely I would want an exact match to month and year?

Thanks,
K

 

[SkipVought]

You do not have MONTH & YEAR. That's only what is displayed.

You have DATES in row 1!!!

0 (Exact Match) will work ONLY if the lookup date ie EXACTLY the same date as one of your row 1 date headings, an MAYBE that's the case.

1 (Less than or equal to Match) will work for ANY lookup date that falls between the dates in your row 1.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Where is your ACTUAL and FORECAST data coming from?

Is any of it in a source file?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[knifey]

Hi Skip,

Thanks for clarifying. I will be using match type 1, although it makes no difference in this situation as I don't have a range of dates to deal with (e.g. 1/4/14-30/4/14). All I have is a total count for each month.

The actuals data will be imported on a weekly basis using VBA from 17 different sources (that all use the same template, phew). This will import to the 'actuals' sheet for use in creating the master sheets rolling forecast.

The forecasted sheet will just be an archive of forecasted values which will change each time the forecast is run (with the new months actuals data).

I hope this all makes sense, it's giving me a headache

[URL unfurl="true"]https://drive.google.com/file/d/0Bx5r9tIItS8VZVpEX1NHVzlyT00/edit?usp=sharing[/url]

Thanks,
K

 

[SkipVought]

imported on a weekly basis using VBA from 17 different sources

Does that mean the the 17 sources are ALL set up like your Actual sheet with multiple dates across columns?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[knifey]

Hi Skip,

Unfortunately no. The 17 sources will each be sending in a single sheet workbook per week. Each workbook will be named with the w/e date that the sales figures cover.
This isn't a problem as I can use some VBA in conjunction with a month/year validation list that the user selects when importing to locate the correct month/year input column.

Thanks,
K