# Formula to extract Data based on Month & Year

1. ## Formula to extract Data based on Month & Year

I have source Data (Profits BR1.xlsx) that contains a heading in A1

I would like to extract the net profit for each Dept on workbook (Profit by Month.xlsx) where the Year and month matched the month and year in the source data file (Profits BR1.xlsx)

It would be appreciated if someone could assist me

I have attached sample data for Sep 2020

2. ## Re: 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)),"")

3. ## Re: Formula to extract Data based on Month & Year

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

4. ## Re: Formula to extract Data based on Month & Year

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.

5. ## Re: Formula to extract Data based on Month & Year

Thanks, I did pick that up

Hope you have a great weekend

6. ## Re: Formula to extract Data based on Month & Year

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.

8. ## Re: Formula to extract Data based on Month & Year

Hi Belinda

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

Kindly check & amend the formula

9. ## Re: Formula to extract Data based on Month & Year

Notice that I have change all "mmm yyyy" text to be real date (1st date of month), formatted as "mmm yyyy"
And create next month by using EOMONTH than drag accross.

In B2:
``Please Login or Register  to view this content.``

10. ## Re: Formula to extract Data based on Month & Year

Thanks for the Help. the Attached file is corrupt, but used your formula on my original workbook I uploaded

What is the significance of +1 in formula

``Please Login or Register  to view this content.``

11. ## Re: Formula to extract Data based on Month & Year

The attachment is not corrupt, but you need to download it then remove the extraneous .xlsx suffix before opening it, leaving just .xls. This happens with files in the old format.

12. ## Re: Formula to extract Data based on Month & Year

Thanks for letting me know Ali. File now opens perfectly

Hope you have a great weekend

13. ## Re: Formula to extract Data based on Month & Year

Hi,

=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))

14. ## Re: Formula to extract Data based on Month & Year

Thanks for the amended formula Belinda

15. ## Re: Formula to extract Data based on Month & Year

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....

16. ## Re: Formula to extract Data based on Month & Year

Thanks for the explanation. It makes perfect sense

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1