Find matching month between datasets and sequence a multiplication between values in specific rows b-CodePudding

I have a sheet for a Project P&L with a data sequenced using the following formula where C2 is 01/01/2020 and E2 is the duration of the project at 39 months:

=EDATE($C$2, SEQUENCE(1, $E$2, 0))

This gives the following:

I have another sheet called Studio Costs which has a calculated per person monthly expense as follows:

For the Project P&L sheet I'd like to create a sequenced formula in I9 for Project Costs based on the number of months determined by E2(39), such that it compares the date in row 1 with the dates in row 1 of the dataset in the Studio Costs sheet, and on finding the matching column, multiply the Monthly Per Employee Costs value in row 5 by the Active Staff value in row 2 of the Project P&L dataset to give the monthly studio costs.

CodePudding user response：

Formula to be placed in I9:

=(I2:P2)*HLOOKUP(I1:P1,'Studio Costs'!$AC$1:$AL$5,5,FALSE)

Change ranges as needed.

EDIT: Final solution to use dynamic ranges author came up with:

=(I2:INDIRECT(ADDRESS(ROW(I2),COLUMN(I2) $E$2-1)))*HLOOKUP(I1:INDIRECT(ADDRESS(ROW(I1),COLUMN(I1) $E$2-1)),'Studio Costs'!$B$1:$KO$5,5,FALSE)