Arrayformula for Index Match multiple columns with date values

Question

I am collecting data on intervals and want to display a summary of that data on a single line in a new sheet.
https://docs.google.com/spreadsheets/d/1EOV4-VwVfwWvhwQ24qkQbCRGxUp74oe0dLWrbj0wiNE/edit#gid=566541214

Grade Data Sheet

Each batch of data comes in by date with a Name, Course and Grade like below

Raw data is like this for a large number of Name / Course / Grade:

Date	Name	Course	Grade
10/1/2022	Joe	Math	65-D
10/15/2022	Joe	Math	58-F
10/30/2022	Joe	Math	50-F

Summary Sheet

Single line that takes each unique Name-Course pair and I'm attempting to lookup a grade for each date column.
(note: I'm trying to extract the dates in the columns dynamically as the Grade Data sheet expands)

So I've successfully extracted the Dates to create new columns, and I am trying to create an index-match that grabs the column date and creates an array of DATE-NAME-COURSE and matches DATE-NAME-COURSE on the Grade Data sheet to return the grade for that student on the Date. The formula works for the first row, but when it fills down it returns the value of the first match.

I can't quite figure out how to reference the single date cell into the array while dynamically filling down. Not sure if I a different approach, but hopefully this makes sense.

=arrayformula(if(len($A2:$A),(index(GradeData!$D2:$D,match(TEXT(C$1,"yyyy-mm-dd")&$A2:$A&$B2:$B,TEXT(GradeData!$A2:$A,"yyyy-mm-dd")&GradeData!$B2:$B&GradeData!$C2:$C,0))),""))`

The goal is to have Grade Data populate automatically, and the Summary page to add a column for each new date and fill data down for each student.

Thanks in advance, I have shared the actual sheet above so you can see the data

I've attempted several different ways but can't quite get the dynamic array matches with the date formatting to work.

=arrayformula(if(len($A2:$A),(index(GradeData!$D2:$D,match(TEXT(C$1,"yyyy-mm-dd")&$A2:$A&$B2:$B,TEXT(GradeData!$A2:$A,"yyyy-mm-dd")&GradeData!$B2:$B&GradeData!$C2:$C,0))),""))

Thank you!

Answer 1

As far as I've seen the issue in your sheet is that the dates of the header do not match the dates of your source data. I've added a new header line:

=LAMBDA(dates,FILTER(dates,regexmatch(dates,"/")))(Transpose(Unique(arrayformula(left(GradeDataVlookup!A4:A,10)))))

And just put an IFERROR in order to avoid all the errors of the values without matches:

=arrayformula(if(len($A$2:$A),iferror(vlookup(TEXT(C$1,"MM/DD/YYYY")&$A2:$A&$B2:$B,GradeDataVlookup!$A:$E,5,FALSE),""),""))

PS: with MAP or MAKEARRAY you could summarize all the table in just one formula