google-sheetsgoogle-sheets-formula

How can I search a Google Sheet for the cell that matches the intersection of the first column and top row of another sheet


I have made a mockup of my google sheet here. (My actual data consists of 105 rows, if that matters.)

I'm stumped once again. I know I've done this before, and I know I found the answer on here, but I cannot recall how I did or correctly formulate my search to turn up the how-to again.

I have a data sheet with:

Parent/Guardian Student (age) Class Class Period
Joni Smith Sally Smith (11) Debate with Mr. Tom Jones 1st Period
Joni Smith Sally Smith (11) U.S. History with Ms. Jan Green 2nd Period
Joni Smith Dan Smith (13) Computer Animation with Mr. Mike Martin 2nd Period
Joni Smith Dan Smith (13) Bicycle Repair with Ms. Jane Good 1st Period
Ben Jones Jill Jones (11) Acrylic Painting with Mr. Bill Bobbins 3rd Period
Ben Jones Jill Jones (11) World History with Mr. Robert Robbins 1st Period
Mary Brown Nancy Brown (11) Drawing: Pen and Ink with Ms. Nancy McDuff 2nd Period
Mary Brown Nancy Brown (11) Acrylic Painting with Mr. Bill Bobbins 3rd Period
Mary Brown Nancy Brown (11) Bicycle Repair with Ms. Jane Good 1st Period
Mary Brown Nancy Brown (11) Gym with Coach Ronald Doubtfire 4th Period
Mary Brown Joe Brown (12) Drawing: Pen and Ink with Ms. Nancy McDuff 2nd Period
Mary Brown Joe Brown (12) Debate with Mr. Tom Jones 1st Period
Mary Brown Joe Brown (12) Gym with Coach Ronald Doubtfire 4th Period
Mary Brown Joe Brown (12) Theater: Shakespeare with Ms. Billie Boxer 3rd Period
Mary Brown Jack Brown (14) Algebra I with Ms. Kathy Cook 3rd Period
John Black Ann Black (13) Swimming with Coach Sally Green 1st Period
John Black Ann Black (13) Theater: Shakespeare with Ms. Billie Boxer 3rd Period
John Black Sam Black (15) Swimming with Coach Sally Green 1st Period
John Black Sam Black (15) U.S. History with Ms. Jan Green 2nd Period
John Black Sam Black (15) Theater: Shakespeare with Ms. Billie Boxer 3rd Period

I want to make another sheet that is:

Student (age) Parent/Guardian 1st Period 2nd Period 3rd Period 4th Period
Sally Smith (11) Joni Smith Debate with Mr. Tom Jones U.S. History with Ms. Jan Green
Dan Smith (13) Joni Smith Bicycle Repair with Ms. Jane Good Computer Animation with Mr. Mike Martin
Jill Jones (11) Ben Jones World History with Mr. Robert Robbins Acrylic Painting with Mr. Bill Bobbins
Nancy Brown (11) Mary Brown Bicycle Repair with Ms. Jane Good. Drawing: Pen and Ink with Ms. Nancy McDuff Acrylic Painting with Mr. Bill Bobbins Gym with Coach Ronald Doubtfire
Joe Brown (12) Mary Brown Debate with Mr. Tom Jones Drawing: Pen and Ink with Ms. Nancy McDuff Theater: Shakespeare with Ms. Billie Boxer Gym with Coach Ronald Doubtfire
Jack Brown (14) Mary Brown Algebra I with Ms. Kathy Cook
Ann Black (13) John Black Swimming with Coach Sally Green Theater: Shakespeare with Ms. Billie Boxer
Sam Black (15) John Black Swimming with Coach Sally Green U.S. History with Ms. Jan Green Theater: Shakespeare with Ms. Billie Boxer

I used UNIQUE to extract all the student names the first column of the results sheet. And TRANSPOSE(UNIQUE) to extract the class periods for the top row. Now I need to search the data sheet for the class title that matches both the student name and the class period.

I have made a mockup of my google sheet here.


Solution

  • You may try:

    =map(A2:A,lambda(Σ,if(Σ="",,map(B1:F1,lambda(Λ,if(left(Λ)="P",xlookup(Σ,'Data Sheet'!B:B,'Data Sheet'!A:A,),ifna(filter('Data Sheet'!C:C,'Data Sheet'!B:B=Σ,'Data Sheet'!D:D=Λ))))))))
    

    enter image description here