Excel Formula to copy value and date to another cell based on date range

[floppy ears]

Hi All,

I need some help with creating an excel formula. On sheet one, i want to add a value ($100) on cell D5 and a date on cell E5.
Then, sheet two has all the months listed (january to december) down one column from cell A9 to A20.
If the date of E5 (on sheet one) equals March, then i want the value on cell D5 (from sheet one, $100) to appear on sheet two, on the cell next to the corresponding month (as it's march, i want to see $100 shown on cell B11, sheet two)

 

[Takae]

Re: First time post - Need Excel Formula to copy value and date to another cell based on date range

A11 of sheet2=VLOOKUP(MONTH(A11),IF({TRUE,FALSE},MONTH(Sheet1!$E$5:$E$15),Sheet1!$D$5:$D$15),2,FALSE)
Shift+Ctrl+Enter(Array formula)

 

[floppy ears]

Re: First time post - Need Excel Formula to copy value and date to another cell based on date range

Thanks for the reply. It doesnt appear to be working and has a #VALUE! error. Can you have one more look at it please?

A11 of sheet2=VLOOKUP(MONTH(A11),IF({TRUE,FALSE},MONTH(Sheet1!$E$5:$E$15),Sheet1!$D$5:$D$15),2,FALSE)
Shift+Ctrl+Enter(Array formula)

 

[Takae]

Re: First time post - Need Excel Formula to copy value and date to another cell based on date range

Sorry, it is not "A11 of sheet2=" , "B11 of sheet2=" is correct.
Did you change the range? And it is not nomal formula. You have to press Shift + Ctrl + Enter simultaneously when set it.
Does the columnE of sheet1 and columnA of sheet2 have date? like 24/5/2017? or only like 5?

 

[floppy ears]

Re: First time post - Need Excel Formula to copy value and date to another cell based on date range

Thanks for the quick reply.

Yes, i changed to 'B11 of sheet2'.

I still having errors. Should the date be 24/5/2017 or 5 or May?

 

[Takae]

Re: First time post - Need Excel Formula to copy value and date to another cell based on date range

Both date should be "24/5/2017" for the formula.
Please tell me what are you going to type on E5?
And what value does A9 of sheet2 have? string? like "May"?

 

[floppy ears]

Re: First time post - Need Excel Formula to copy value and date to another cell based on date range

All worked, thank you very much!