Lookup or Index/Match formula

ajyk27

I want to designate a Forecast Month and have data for that matching month populate in a specific column/cell that corresponds to the month being reviewed. Is this possible?

Larry

MonthAllocationCalc..jpg

I do not have full content of the sheet you provided a screenshot of but I believe the what you are looking for would be to set up the sheet as I have in the screen shot & then use the formulas/setup noted below.

[MONTH/YEAR REFERENCE ROW]: I use the the "Month/Year" text in row 1 as a reference in the formulas below for each month. I manually added this to make the formula as simple as possible.

Forecast Month: =IFERROR(MONTH([Forecast Date]@row) + "/" + YEAR([Forecast Date]@row), "")

This allows me to select any date in the month I want to indicate as the forecast month & matches the return value to the Reference row 1 if it is the corresponding month.

Month Formulas(October2024): =IF($[Forecast Month]@row = [October 2024]$1, $[Commitment Totals]@row, 0)

The "$" absolute references allow me to drag this formula right & down for as many months as needed so long as I put the "Month/Year" text in row 1. I also can put the "Commitment Totals" for the line item in 1 field & the formulas will allocate it ot the corresponding month based on my FOrecast Date automatically.

Larry

MonthAllocationCalc..jpg

I do not have full content of the sheet you provided a screenshot of but I believe the what you are looking for would be to set up the sheet as I have in the screen shot & then use the formulas/setup noted below.

[MONTH/YEAR REFERENCE ROW]: I use the the "Month/Year" text in row 1 as a reference in the formulas below for each month. I manually added this to make the formula as simple as possible.

Forecast Month: =IFERROR(MONTH([Forecast Date]@row) + "/" + YEAR([Forecast Date]@row), "")

This allows me to select any date in the month I want to indicate as the forecast month & matches the return value to the Reference row 1 if it is the corresponding month.

Month Formulas(October2024): =IF($[Forecast Month]@row = [October 2024]$1, $[Commitment Totals]@row, 0)

The "$" absolute references allow me to drag this formula right & down for as many months as needed so long as I put the "Month/Year" text in row 1. I also can put the "Commitment Totals" for the line item in 1 field & the formulas will allocate it ot the corresponding month based on my FOrecast Date automatically.

ajyk27

Thank you Larry, I actually needed the information under the dates to feed back into the Total Commitment Column, but you got me started in the right direction (I added a column and entered the month I am looking for the data on for each row and used the Forecast Month formula you gave me). I was then able to use the INDEX/MATCH formula to get what I needed. Thank you very much!