Matching Date Values and exporting data to identified row/column

jm25

New member
Joined
Sep 19, 2013
Messages
2
Reaction score
0
Points
0
I am new to Excel programming and formulas and im not sure how to best approach this, I have a large spreadsheet (500+ Row) that contains data as shown below. I am looking for a way to automate or formalise the following actions:

Take the date value in the date column, and match it to the corresponding Column value, eg, 1/10/2013 matches Column OCT-13. Once determined, I want the price value put into that cell of the corresponding row. I hope the example below clarifies this because I dont know how to explain it :). Thanks for any assistance given.
Price
Date
OCT-2013
NOV-13
Dec-13
Jan-14
5
1/10/2013
5
6
1/1/2014
6
9
1/11/2013
9
15
1/12/2013
15
 
Last edited:

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
Good afternoon,

If what I understand is correct then this should work. Otherwise I may need some additional guidance :). Hope this helps.

Best of luck,
 

Attachments

  • Date_Match_Sample.xlsx
    9.3 KB · Views: 23

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
Hello
Heres another possibility:

=IF(AND(MONTH(C$1)=MONTH($B2), YEAR(C$1)=YEAR($B2)), $A2, "")

Placed in C2 and copied down and across the table.

 

jsuarezg

New member
Joined
Sep 21, 2013
Messages
24
Reaction score
0
Points
0
You can use a VLOOKUP function, this function search the value that you selected in a database and returns information of the column that you chooser of this database. In myexceltutorial dot com you can learn more about this function. Subscribe and look in the Wiki
 

Kevin@Radstock

New member
Joined
Oct 5, 2012
Messages
52
Reaction score
0
Points
0
Excel Version(s)
365
Hi

Assuming that the data is in A1:F5 and the dates in the top row are also the 1st of each month, you could use the IF function!
In C2, copy across and down: =IF($B2=C$1,$A2,"")
 

jm25

New member
Joined
Sep 19, 2013
Messages
2
Reaction score
0
Points
0
@bgoree09: Thanks for that, was very helpful, I actually got your one working accross my spreadsheet Thursday night, sorry for the late response, thanks a million was really appreciated.

Thanks everyone else for the suggestions. Theyre all helpful with regard to actually learning how excel formula work.
 

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
Very good. You're very welcome. Hercules' formula does the same thing, but his is a bit more concise. Not really sure why I threw a dash in the concatenate, but it doesn't hurt anything. Credit where credit is due :smile:.
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
Very good. You're very welcome. Hercules' formula does the same thing, but his is a bit more concise. Not really sure why I threw a dash in the concatenate, but it doesn't hurt anything. Credit where credit is due :smile:.

Thanks
One of the things I enjoy is how many different ways can be found to resolve a given problem.
 
Top