SOLVED

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

Brass 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

 

 

 

 

 

 

 

 

10 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 (Brass Contributor)
Solution
Yes, the formula uses the TODAY function which obtains the date from your computer's date.
Hi Patrick,

I hope you are well ? You kindly helped me with a formula for a problem i was was trying to overcome in excel.
I need to tweak the solution you provided but can't think of where to start. Are you kindly able to help again ?

Essentially, i want to modify the formula below to look back at the previous month instead of the current month. Can you advise / help ?

=INDEX($A$2:$L$10,ROW()-1,MATCH(TEXT(TODAY(),"mmm"),$A$1:$L$1,0))
Hi Raj, slight change to the formula to pull previous month:
=INDEX($A$2:$L$10,ROW()-1,MATCH(TEXT(TODAY(),"mmm"),$A$1:$L$1,0)-1)

Thank you @Patrick2788 

 

Did the trick. Appreciate your help :)

1 best response

Accepted Solutions
best response confirmed by Mr_Raj_C (Brass Contributor)
Solution
Yes, the formula uses the TODAY function which obtains the date from your computer's date.

View solution in original post