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=Λ))))))))
- ranking formula start from negative number
- How can I search a Google Sheet for the cell that matches the intersection of the first column and top row of another sheet
- Javascript case sensitive
- Can I alter a formula using the cell number instead of its cell value?
- Dynamically referencing worksheets to dynamically fill a table based on a condition
- How do you add UI inside cells in a google spreadsheet using app script?
- How can I force data input into cells in Google Sheets to be in a desired date format?
- How do I find a partial match from a data table to an individual cell?
- Highlighting non-first instance duplicates of words and counting highlighted cells
- Lock google sheets for data entry after a wrong entry
- Schedule View: INDEX MATCH from table and return multiple cells into one
- How to Show/Hide rows in google sheets using a script?
- Popping a Modal Dialog when changing the drop down entry in Google Sheets
- Transfer background color from cell range to drop down options
- How can I match a smart chip using SUMIF?
- List Google drive folder contents to google sheets with only new files
- Trying to figure out why my GoogleSheets Date formula is not working
- Count if name matches and outcome is complete
- Google Sheets Row Number of last row in a defined table
- How can I use INDEX and MATCH along with conditions to filter data into different tables?
- Google Sheets formula to remove all punctuation excluding period (.)
- Formula to find missing serial numbers
- Google Sheet Vlookup Multiple Criteria
- Copy Data from one column and past at the end of another column
- Query formula not grouping properly
- Comparing text fields that contain dates in Google Sheets
- Signature Pad (Canvas) to Google Sheets
- How to use the cell number instead of its value in a Filter + Importrange
- regexreplace multiple columns for football data
- I want to use a macro to check a value in a sheet and return one of two values