# Formula to extract data based on Month & Year

• I have source data on Sheet1 for Jul 2017 (cell K1) on a particular workbook which is imported-see sample Data Below

I have July 2017 on another Workbook (K4) and would like to extract the figures for each dept pertaining to the month Applicable

I would like a formula to extract the data for the applicable month in J1 for e.g. data in row 2 applicable for Jul 2017 which in this example is-56,986, row3 which is -3,606 etc

It would be appreciated if someone can assist me

I have also posted on Mr Excel.com

https://www.mrexcel.com/forum/…ata-based-month-year.html

## Files

• Re: Formula to extract data based on Month &amp; Year

I have come up with this solution. It would be appreciated if there is a better solution if someone come kindly assist me

## Files

• Re: Formula to extract data based on Month &amp; Year

Hello,

There are several steps required to build the formula you need ...

The first deals with the fact you are using a date (which is a number ...) in Profits by Months ... and the Branch worksheet is using actual text ...

So in cell K1, you need following transformation formula ...

Code
``=TEXT(DATE(1,MONTH(K4),1),"mmm")&" "&YEAR(K4)``

Hope this will help

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Re: Formula to extract data based on Month &amp; Year

• Re: Formula to extract data based on Month &amp; Year

Hello again,

Once you have made the transformation, then you can move on to the next two steps ...

add a match formula in cell K2

Code
``=MATCH("*"&K\$1&"*",'[Branch Profits.xlsx]Sheet1'!\$1:\$1,0)``

Code
``=OFFSET('[Branch Profits.xlsx]Sheet1'!\$A\$1,ROW()-4,K\$2)``

Hope this will help

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Re: Formula to extract data based on Month &amp; Year

Thanks for the help much appreciated

• Re: Formula to extract data based on Month &amp; Year

You are welcome ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!