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=Λ))))))))
- Google Apps Script creates sheets version of excel file
- How to fix a #VALUE error when trying to pull an entire row of information from multiple sheets
- If var isnumber, for script
- Custom sort order according to cell value
- Google Sheets Script new Date() with timezone is providing wrong time
- Trying to Email Multiple PDF Blobs in Sheets Javascript
- HTTP Google Sheets API v4 how to access without OAuth 2.0?
- List All Workdays between Start and End Dates Displaying Associated Name and Details in Other Columns
- How do I edit the response of a form submission in Google Forms/Sheets?
- How can I count repeats in a a column but reset count on non-repeats, in an array formula
- How do I stop Google AppScripts (Sheets) from creating two protected range entries
- Transfer Data from one sheet to another
- googlesheet get last non blank row from specific rows
- Send Mail after Form Submit to Google Sheet
- IFS with multiple conditions check in Google Sheet
- Can I find the value of a cell B49 that changes value according to the value of the cell F2 ( in Google sheet)
- What is the GoogleJsonResponseException error when trying to get Drive.comments.list()?
- Google Sheets: extract text between two characters
- Google Sheets REGEXEXTRACT to extract text value for URL query-parameter
- How to access the cell next to the selected cell in Google sheet using script?
- How to make the cancel Button work in Google App Script Browser Input Box
- Dowload public Google Spreadsheet
- Index Match using Tables in Google Sheets not giving correct results
- How to extract url from Google Sheet Cell with anchor text hyperlink
- How do you deal with multiple values in a single cell separated by comma?
- How to send content as comment to a specific cell in google sheets
- Get Google Sheet by ID?
- Excel getting names from biggest values with Regextract
- How to create a formula that will repeat a range by splitting one column and joining with the other column?
- Google Sheets Splitting Text to Rows and Duplicate Surrounding Rows Entries