Formula: Index Match Based on Today to reference Current Month Column

Allison Horn

Hi there -

Looking to build a formula in a "Current Month" column that pulls the data from a column to display the current month for a variety of rows based on various metrics.

Current Structure

1. Months 1 -12 are 12 months of the year
2. I have a "Growth Plan Date" to reference Row 1 for each month, and it populates the names of the month. For example, the growth plan date is 10/24/2022, Month 1 would be October, Month 12 would be September
3. Production Goal row is auto-populated for the year

Formula Assistance

Looking for a formula in the "Current Month" column that says if today is October 24, look at row 1 and find the column for October and then populate the Production Goal Row in October.

Challenge (Month 1-12) months in row 1 change based on the date and will be different for each sheet, so just referencing Month in row 1 is critical. Thinking Index Match but I can't get it to work.

I also added the name of the current month in row 1 under the "Current Month" if that can be utilized as well instead of a "Today" option.

Allison Horn

Found a solution.

=INDEX([Month 1]5:[Month 12]5, 1, MATCH($[Current Month]$1, $[Month 1]$1:$[Month 12]$1, 0))

Andrée Starå

Hi @Allison Horn

I hope you're well and safe!

Excellent! Glad to hear you got it working!

You could add the current month part to the formula so you could remove the column, or do you want to have the column as it is?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Allison Horn

The current month is needed to populate a mid-month dashboard to check the status or production as it is entered daily on another sheet.