SOLVED

Help with a formula for returning a value in a cell based on column heading & date

%3CLINGO-SUB%20id%3D%22lingo-sub-3344651%22%20slang%3D%22en-US%22%3EHelp%20with%20a%20formula%20for%20returning%20a%20value%20in%20a%20cell%20based%20on%20column%20heading%20%26amp%3B%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3344651%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Excel%20Community%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20appreciate%20your%20help%20with%20a%20problem%20i%20am%20trying%20to%20resolve.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20the%20attached%20sheet%20for%20example.%26nbsp%3BDate%20base%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20essentially%20i%20want%20to%20populate%20Column%20M%20(Current%20Forecast)%20with%20the%20corresponding%20data%20based%20on%20the%20month%20we%20are%20in.%20So%20if%20we%20are%20in%20May%2C%20i%20want%20May's%20data%20in%20Column%20M.%20When%20we%20hit%20June%2C%20i%20want%20June's%20data%20in%20Column%20M.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20someone%20can%20help%20me%20out%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ERaj%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22585%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2297%22%3EApr%3C%2FTD%3E%3CTD%20width%3D%2239%22%3EMay%3C%2FTD%3E%3CTD%20width%3D%2239%22%3EJun%3C%2FTD%3E%3CTD%20width%3D%2239%22%3EJul%3C%2FTD%3E%3CTD%20width%3D%2239%22%3EAug%3C%2FTD%3E%3CTD%20width%3D%2239%22%3ESep%3C%2FTD%3E%3CTD%20width%3D%2239%22%3EOct%3C%2FTD%3E%3CTD%20width%3D%2239%22%3ENov%3C%2FTD%3E%3CTD%20width%3D%2239%22%3EDec%3C%2FTD%3E%3CTD%20width%3D%2239%22%3EJan%3C%2FTD%3E%3CTD%20width%3D%2239%22%3EFeb%3C%2FTD%3E%3CTD%20width%3D%2239%22%3EMar%3C%2FTD%3E%3CTD%20width%3D%2259%22%3ECurrent%20Forecast%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%C2%A3100.00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%C2%A3200.00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%C2%A350.00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%C2%A340.00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%C2%A312.00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%C2%A3200%2C000.00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%C2%A3100%2C000.00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%C2%A30.00%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%C2%A3300%2C402.00%3C%2FTD%3E%3CTD%3E%C2%A30.00%3C%2FTD%3E%3CTD%3E%C2%A30.00%3C%2FTD%3E%3CTD%3E%C2%A30.00%3C%2FTD%3E%3CTD%3E%C2%A30.00%3C%2FTD%3E%3CTD%3E%C2%A30.00%3C%2FTD%3E%3CTD%3E%C2%A30.00%3C%2FTD%3E%3CTD%3E%C2%A30.00%3C%2FTD%3E%3CTD%3E%C2%A30.00%3C%2FTD%3E%3CTD%3E%C2%A30.00%3C%2FTD%3E%3CTD%3E%C2%A30.00%3C%2FTD%3E%3CTD%3E%C2%A30.00%3C%2FTD%3E%3CTD%3E%C2%A30.00%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3344651%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3344738%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20formula%20for%20returning%20a%20value%20in%20a%20cell%20based%20on%20column%20heading%20%26amp%3B%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3344738%22%20slang%3D%22en-US%22%3ETry%20this%3A%3CBR%20%2F%3E%3DINDEX(%24A%242%3A%24L%2410%2CSEQUENCE(9)%2CXMATCH(TEXT(TODAY()%2C%22mmm%22)%2C%24A%241%3A%24L%241))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3344782%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20formula%20for%20returning%20a%20value%20in%20a%20cell%20based%20on%20column%20heading%20%26amp%3B%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3344782%22%20slang%3D%22en-US%22%3EHi%20Patrick%2C%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EUnfortunately%20that%20did%20not%20work.%20It%20came%20up%20with%20%23NAME%3F.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EWhen%20i%20checked%20the%20error%2C%20it%20said%20%22Invalid%20Name%20Error%22.%20Assuming%20it%20didn't%20like%20something%20in%20the%20formula.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3344894%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20formula%20for%20returning%20a%20value%20in%20a%20cell%20based%20on%20column%20heading%20%26amp%3B%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3344894%22%20slang%3D%22en-US%22%3EIt%20appears%20you're%20not%20on%20365.%20You%20may%20use%20this%20instead%20and%20fill%20down%3A%3CBR%20%2F%3E%3DINDEX(%24A%242%3A%24L%2410%2CROW()-1%2CMATCH(TEXT(TODAY()%2C%22mmm%22)%2C%24A%241%3A%24L%241%2C0))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3344922%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20formula%20for%20returning%20a%20value%20in%20a%20cell%20based%20on%20column%20heading%20%26amp%3B%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3344922%22%20slang%3D%22en-US%22%3EFantastic%20Patrick%2C%20thanks%20for%20that%2C%20worked%20a%20treat.%3CBR%20%2F%3E%3CBR%20%2F%3EJust%20to%20confirm%2C%20when%20the%20month%20changes%20to%20June%2C%20it%20will%20take%20data%20from%20the%20June%20column%20%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3344969%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20formula%20for%20returning%20a%20value%20in%20a%20cell%20based%20on%20column%20heading%20%26amp%3B%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3344969%22%20slang%3D%22en-US%22%3EYes%2C%20the%20formula%20uses%20the%20TODAY%20function%20which%20obtains%20the%20date%20from%20your%20computer's%20date.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3345025%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20formula%20for%20returning%20a%20value%20in%20a%20cell%20based%20on%20column%20heading%20%26amp%3B%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3345025%22%20slang%3D%22en-US%22%3EGreat.%20Thanks%20for%20your%20help%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E
New Contributor

Dear Excel Community, 

 

I would appreciate your help with a problem i am trying to resolve.

 

Please see the attached sheet for example. Date base formula

 

So essentially i want to populate Column M (Current Forecast) with the corresponding data based on the month we are in. So if we are in May, i want May's data in Column M. When we hit June, i want June's data in Column M.

 

Hope someone can help me out

 

Thanks in advance


Raj

 

AprMayJunJulAugSepOctNovDecJanFebMarCurrent Forecast
£100.00            
£200.00            
£50.00            
£40.00            
£12.00            
£200,000.00            
£100,000.00            
£0.00            
£300,402.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00£0.00

 

 

 

 

 

 

 

 

6 Replies
Try this:
=INDEX($A$2:$L$10,SEQUENCE(9),XMATCH(TEXT(TODAY(),"mmm"),$A$1:$L$1))
Hi Patrick, 

Unfortunately that did not work. It came up with #NAME?. 

When i checked the error, it said "Invalid Name Error". Assuming it didn't like something in the formula.
It appears you're not on 365. You may use this instead and fill down:
=INDEX($A$2:$L$10,ROW()-1,MATCH(TEXT(TODAY(),"mmm"),$A$1:$L$1,0))
Fantastic Patrick, thanks for that, worked a treat.

Just to confirm, when the month changes to June, it will take data from the June column ?
best response confirmed by Mr_Raj_C (New Contributor)
Solution
Yes, the formula uses the TODAY function which obtains the date from your computer's date.