SOLVED

New Contributor

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

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

6 Replies

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

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

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

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.

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

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))

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

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

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

Yes, the formula uses the TODAY function which obtains the date from your computer's date.