Forum Discussion
Solved
Help with a formula for returning a value in a cell based on column heading & date
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
| Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar | Current 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 |
- Yes, the formula uses the TODAY function which obtains the date from your computer's date.
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))
