Formula to extract Data based on Month & Year

Hi,
Do you mean like in the attached? I copied the tabs to one file for convenience.
so - your left tab is the source data, right?

and sheet2 would collate your data?
Do you have different tabs/sheets for each month? where will you take the rest of data from to fill in sheet 2?


The formula I applied in Sheet2 cell B2 and across:
=IF(AND(MONTH(B$1)=MONTH('Source Data'!$F$1),YEAR(Sheet2!B$1=YEAR('Source Data'!$F$1))),INDEX('Source Data'!$B$3:$B$6,MATCH(RIGHT($A2,4),'Source Data'!$A$3:$A$6,0)),"")

Hi Belinda


what you have done is perfect. I will teat on actual data nd if I need any further help, I will let you know


I am familiar with Index/match, but your formula is awesome

I fully understand the logic since going through the entire formula

Great, just notice I have changed the text in Sheet 2 to say "Dep1" instead of "Dept1", so that when searching on source data it will find a match, meaning the text in both sheets should be the same.

Thanks, I did pick that up


Hope you have a great weekend

And another thing
(I noticed another thing that might interrupt the formula) I don't know how many departments you have to cover, but the formula is good for a dep number that don't exceed 1 figure (1-9), if the dep number contains 2 figures (Dep10 and so on) - you'd better use the below :

b2 and down:
=IF(AND(MONTH(B$1)=MONTH('Source Data'!$F$1),YEAR(Sheet2!B$1=YEAR('Source Data'!$F$1))),INDEX('Source Data'!$B$3:$B$6,MATCH(MID($A2,SEARCH("-",$A2)+1,99),'Source Data'!$A$3:$A$6,0)),"")

Thank you and have a nice weekend too.

Thanks for the heads up

Hi Belinda

I tried to adapt your formula to a similar problem, but get #ref!


Kindly check & amend the formula

Thanks for letting me know Ali. File now opens perfectly

Hope you have a great weekend

Hi,
please use the below adjusted formula in B2 and down:

=INDEX('Source Data'!$B$2:$M$5, MATCH(MID($A2,SEARCH("-",$A2)+1,99),'Source Data'!$A$2:$A$5,0),MATCH(MONTH(B$1)&YEAR(B$1),MONTH('Source Data'!$B$1:$M$1)&YEAR('Source Data'!$B$1:$M$1),0))

Thanks for the amended formula Belinda

You're welcome.
The "+1 " in the below formula for example is for excel to find the placement of the sign "-" and bring all characters after this sign (from left to right).
so if you have this expression "Net Profit Achieved Month-Dept4" - The formula will bring "-Dept4" , but if you want to lose the "-" (which is what we need here) - you will add 1 to instruct the formula to start 1 character after the "-" sign, so that the result will be "Dept4".

MID($A2,SEARCH("-",$A2)+1,99)

Hope this makes sense....

Thanks for the explanation. It makes perfect sense