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