Google sheets: match by two rows and extract to different sheet-CodePudding

I am building a reporting sheet that automatically extracts hours worked by person, date, and project from an aggregated sheet and transposes the data horizontally to a different spreadsheet. The origin sheet looks like this:

The target sheet looks like this for each employee:

I am trying to figure out a function that extracts the relevant data from origin based on the name, project, and month values contained in row 2 of the target sheet. The origin sheet lists several more employees horizontally and several years vertically. I know I need the importrange function, but I am struggling with finding an extraction function based on the name, project, and day.

I would be very grateful for any suggestions :) Thanks in advance!

CodePudding user response：

Try this series of formulas:

Output (D7):

=transpose(query({Sheet1!C3:C,indirect("Sheet1!"&address(3, Y3, 4)&":"&left(address(3, Y3, 4), 1))}, 
           "select Col2 where 
                   Col1 > date '"&text(date(C2, month(A2&1),), "yyyy-MM-dd")&"' and 
                   Col1 < date '"&text(date(C2, month(A2&1)   1, 1), "yyyy-MM-dd")&"'"))

I also added some formulas in other cells that are needed by the output, these are:

Y1 = match(L2, Sheet1!A1:1)

Y2 = if(L2 = query(transpose(Sheet1!1:1), "where Col1 is not null order by Col1 desc limit 1"), 
        counta(Sheet1!1:1) countblank(Sheet1!1:1), 
        match(regexextract(join(",", Sheet1!1:1), L2&",*([^,]*),"), Sheet1!1:1))

Y3 = Y1   match(P2, indirect("Sheet1!"&ADDRESS(2, Y1   1, 4)&":"&ADDRESS(2, Y2 - 1, 4)))

Output (D7):

Output: