Populate a column of cells based on a table

[badenmorgan]

Hi, this is my first post, apologies if it's a bit simple.

I'd like to be able to automatically generate a date from a list based on two preceding entries. E.g. I have this table on the left referring to weeks, days and dates. If I enter in the blank cells under Week "2", under Day "4", I'd like excel to automatically associate it with the date 30/01/2014 and populate the cell with this date automatically. Is there a way of doing this with a formula, or any way at all? Sorry for the lines but couldn't display the table very well without them. Most grateful, Simon


Week_Day__Date_______ Week___Day______Date
1_____1____20/1_______ 2______ 4________
1_____2____21/1
1_____3____22/1
2_____1____27/1
2_____2____28/1
2_____3____29/1
2_____4____30/1

[FDibbins]

Hi and welcome to the forum

Our date formats are different, so you may need to play around with this, but give it a shot. Use this ARRAY formula, copied down...
=DATEVALUE(2014&"/"&INDEX($C$2:$C$8,MATCH(D2&E2,$A$2:$A$8&$B$2:$B$8,0)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[badenmorgan]

Thank you FDibbins.

I tried this and got the following error message:

errror.jpg

If you could advise further I'd appreciate it.

Thanks, Simon