Dynamic array formula to summary weekly data (like a pivot table)

Question

I have been wrestling with ChatGPT (and losing) trying to find an answer to this one.

This is a sample of weekly data displayed in A2:D43021 of Sheet1.

DATE	ARTIST	SONG	THIS WEEK
1/3/1970	Diana Ross & The Supremes	Someday We'll Be Together	1
1/3/1970	Jackson 5	I Want You Back	2
1/3/1970	James Brown	Ain't It Funky Now (Part 1)	3
1/3/1970	Jr. Walker & The All Stars	These Eyes	4
1/3/1970	Gladys Knight And The Pips	Friendship Train	5
1/3/1970	Marvin Gaye & Tammi Terrell	What You Gave Me	6
1/3/1970	Johnnie Taylor	Love Bones	7
1/10/1970	Jackson 5	I Want You Back	1
1/10/1970	Diana Ross & The Supremes	Someday We'll Be Together	2
1/10/1970	James Brown	Ain't It Funky Now (Part 1)	3
1/10/1970	Jr. Walker & The All Stars	These Eyes	4
1/10/1970	Johnnie Taylor	Love Bones	5
1/10/1970	Marvin Gaye & Tammi Terrell	What You Gave Me	6
1/10/1970	Gladys Knight And The Pips	Friendship Train	7

Sheet2 currently looks like this.

1ST DATE	1ST POS	ARTIST	SONG	01/03/70	01/10/70
		Diana Ross & The Supremes	Someday We'll Be Together
		Jackson 5	I Want You Back
		James Brown	Ain't It Funky Now (Part 1)
		Jr. Walker & The All Stars	These Eyes
		Gladys Knight And The Pips	Friendship Train
		Marvin Gaye & Tammi Terrell	What You Gave Me
		Johnnie Taylor	Love Bones

I'm looking for a single dynamic array formula for Sheet2 cell E2 (the cell under the date 01/03/70).

This formula should take the artist and song values in that row and date value in that column, find the row on Sheet1 with those values, and return the corresponding THIS WEEK value.

It should then spill those returned values down all rows and across all columns starting at Sheet2 cell E2.

Based on above example of Sheet1, Sheet2 should end up looking like this.

1ST DATE	1ST POS	ARTIST	SONG	01/03/70	01/10/70
		Diana Ross & The Supremes	Someday We'll Be Together	1	2
		Jackson 5	I Want You Back	2	1
		James Brown	Ain't It Funky Now (Part 1)	3	3
		Jr. Walker & The All Stars	These Eyes	4	4
		Gladys Knight And The Pips	Friendship Train	5	7
		Marvin Gaye & Tammi Terrell	What You Gave Me	6	6
		Johnnie Taylor	Love Bones	7	5

I have used Power Query to create a pivot table but I would like to do more calculations directly from Sheet1.

My pc is too underpowered to handle lookup formulas in each cell.

I've worked with versions of this formula for cell E2 but I can't get a grip on the logic for populating both vertically and horizontally.

=LET(
    data,'Sheet1'!A2:D43021,
    artist, $C$2:$C$4557,
    song, $D$2:$D$4557,
    datehdr, $E$1:$TF$1,
    BYROW(artist&song,
        LAMBDA(row,
            MATCH(
                TRUE,
                (row=INDEX(data,,2)&INDEX(data,,3))*(INDEX(datehdr,,1)=INDEX(data,,1)),
                0
            )
        )
    )
)

Any suggestions would be greatly appreciated.

Answer 1

Try using the following formula, using LAMBDA() helper function MAKEARRAY() the following will generate the whole array as is mentioned in your OP:

---

• Formula used in cell C1 of Sheet2

=LET(
     _Data, Sheet1!A2:D15,
     _ArtistSong, CHOOSECOLS(_Data,2,3),
     _Uniq, UNIQUE(_ArtistSong),
     _Date, TAKE(_Data,,1),
     _Output, MAKEARRAY(ROWS(_Uniq),COLUMNS(DROP(C1:F1,,2)), LAMBDA(r,c,
     TOROW(FILTER(TAKE(_Data,,-1), (INDEX(_Uniq,r,1)=INDEX(_ArtistSong,,1))*
     (INDEX(_Uniq,r,2)=INDEX(_ArtistSong,,2))*(INDEX(DROP(C1:F1,,2),c)=_Date),"")))),
     HSTACK(_Uniq, _Output))

---

As per new update by OP:

=LET(
     α, C2:D8,
     δ, E1:F1,
     MAKEARRAY(ROWS(α),COLUMNS(δ),LAMBDA(r,c,
     FILTER(Sheet1!D2:D15,(INDEX(α,r,1)=Sheet1!B2:B15)*
     (INDEX(α,r,2)=Sheet1!C2:C15)*(INDEX(δ,c)=Sheet1!A2:A15),""))))

---