VLookup+ Monthly Data

[Chris26]

Hi I am trying to save some time Cut and Paste.
I have values as follows for each month
Col A Col B
Jan 5.15
Feb 3.20
-
Dec 6.75

I then have a 100 years of daily data
Col X Col Y
1 Jan 1900
2 Jan 1900
3 Jan 1900
-
31 Dec 1999

I want to apply the value for January (5.15) to Col Y for 1-31st January
00-99, then same for Feb, march etc.
I need to do this fairly frequently to copy into another program.

Probably a simple answer, I have used VLookup before but cant get it to work
for this.

Many thanks
Chris

 

[Bernard Liengme]

I will assume:
1) In Col A you have text not dates
2) in Col X you have real dates

In AA1 use the formula
=CHOOSE(MONTH(X1),"Jan","Feb",
"Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
Copy it down the column; giving text like: Jan, Feb ..

In X1 use the formula
=VLOOKUP(AA1,$A$1:$B$12,2,FALSE)
Copy it down the column

I will leave it to use to combine the AA1 formula into the X1 formula if so
desired

best wishes

 

[Chris26]

That worked, Thank you

I will assume:
1) In Col A you have text not dates
2) in Col X you have real dates

In AA1 use the formula
=CHOOSE(MONTH(X1),"Jan","Feb",
"Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
Copy it down the column; giving text like: Jan, Feb ..

In X1 use the formula
=VLOOKUP(AA1,$A$1:$B$12,2,FALSE)
Copy it down the column

I will leave it to use to combine the AA1 formula into the X1 formula if so
desired

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

 

[Teethless mama]

=CHOOSE(MONTH(X1),"Jan","Feb",

"Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

shorter version:

=TEXT(X1,"mmm")

 

[Bernard Liengme]

Good point! I must have more coffee!
best wishes

 

[Bernard Liengme]

Now I recall why I used the longer method: it allows for non-standard text
abbreviations in the OP's data