Formula to convert monthly data into weekly data - help needed

Dear Excel Forum members,

I have a difficult problem in converting monthly data to weekly data.
Context: I have 21 EU countries with weekly data regarding the price of gasoline between 2005-2014 (1 year contains 52 data for each year). But the inflation rate is only reported in monthly data. So, I would like to convert monthly inflation data into weekly data to match the price of gasoline for each country and each year. It is time consuming filling manually each cell through Copy-Paste, and I would like to ask you if there is some formula to ease my work?
The weekly data looks like this (please be aware that 200501 - in case of weekly data represent the week number 1 of year 2005):
Country Date Pricegasoline
AT 200501 916
AT 200502 916
AT 200503 907
AT 200504 910
... ... ...
AT 200552 1038

And the inflation rate is monthly (please be aware that 200501 represent month number 1 of year 2005):

Country Date Inflation
AT 200501 99.15
AT 200502 99.48
... ... ...
AT 200512 100.69

How can I convert inflation from monthly to appear in weekly, meaning for the first four weeks of January 2005 inflation rate should appear 99.15, and then for the next 4 weeks of February the inflation rate should appear 99.48 and so on?
Meaning:
Ctry Date(week) Pricegasoline Inflation
AT 200501 916 99.15
AT 200502 916 99.15
AT 200503 907 99.15
AT 200504 910 99.15
AT 200505 907 99.48
AT 200506 919 99.48
and so on.
I would like to mention that I did not declare in Excel "Date (weekly and montly)" as date they appear just as simple numbers.
On 05.08.2014 the user FSmit posted as similar problem and user ragulduy replied with a possible solution. Unfortunately, I've tried it and it does not work for me. I would like to mention also that I am using MS Office 2010.
Please find attached the example in .xlsx format.
I would like to thank you in advance for your help and time.

Best regards, Marian

The main issue is that 12 * 4 week months = 48 weeks: so how do we "allocate" the 4 (52-48) extra weeks? E.g. Week 5 is part January and part February.

Dear John Topley,

What if I would transform "200501" from a simple number and declare that as week 1 of 2005, not a number but a date? Would that help? Or it is much more complicate than this? Meaning, it should start with days, then transformed in weeks and then apply the right formula. It is beyond my understanding, how should this work.
Thanks for your reply.

Try this in D2:

=DATE(LEFT(B2,4),1,RIGHT(B2,2)*7-6)

Copy down.

Many thanks Phuocam. I got something like 1/1/2005 meaning it is declared in weeks. Now the question how can I allocate monthly data for each week? I have almost 14000 observations for 21 countries, so I really need a formula.

Ok. So both columns - Monthly and Weekly were declared as data and not just simple numbers. Once I have this transformations, how can I allocate January 2005 inflation to appear in the first 5 weeks the same, meaning 99.15. Then for 4 weeks of February 2005 the inflation should appear 99.48, and so on?

To be honest, we don't need to be so strict or the allocation of data should not match rigorously weekly and monthly. There is no problem if midweeks take month N+1 inflation rate. Ideally would be the exact match, but if it is not possible then I'm happy with an allocation that has few mismatches.

With Phocam's formula in column D

in E2 and copy down

=INDEX($H$2:$H$25,MATCH(SUBSTITUTE(D2,"/","M"),$G$2:$G$100),0)

Dear Both John Topley and Phuocam,

You are amazing! It works! You saved me hours and hours of Copy-Paste.
I will strongly recommend this forum to my colleagues.
Many thanks to both of you. This thread can be declared as SOLVED.

Best, Marian

You are very welcome.