Just a collection of formulas I use from time-to-time in Google Sheets.
Query two sheets to find rows with a cell matching another cell’s value.
In this case, I was looking up employee type (Admin, teacher, support) in column I (Col9) by finding the email address (Col2) that corresponds to the email address in the current sheet’s column (K) and row (2).
=QUERY(IMPORTRANGE({"##### URL of first Google spreadsheet with data . ####","AllFaculty!A$2:M");>IMPORTRANGE("##### URL of second Google spreadsheet with data . ####","AllAdmin!A$2:M")},"Select Col9 where Col2 CONTAINS '"&K2&"'")
Get Unique PowerSchool parent email addresses from mother, father and guardian email fields.
We export parent email addresses out of PowerSchool using the cnt1_email, cnt2_email and guardianemail fields from the Students table. This often results in duplicates as cnt1 & cnt2 are often both in the guardian field and sometimes even the cnt1 & 2 fields have 2 addresses separated by a comma. To get the unique values, we use this little gem which assumes the three exported fields land in columns D, E, & F.
=TRANSPOSE(UNIQUE(QUERY(TRANSPOSE({IF(D2<>"",SPLIT(LOWER(SUBSTITUTE(D2," ","")),","),""),IF(E2<>"",SPLIT(LOWER(SUBSTITUTE(E2," ","")),","),""),IF(F2<>"",SPLIT(LOWER(SUBSTITUTE(F2," ","")),","),"")}),"SELECT Col1 WHERE Col1 <> ''")))
If that’s entered in column G, then columns G-J will have only the unique addresses for each student.
If you just want all the unique email addresses for all parents, just use this in column K:
=SORT(UNIQUE({G:G;H:H;I:I;J:J}))
INDEX(MATCH): An Improvement Over VLOOKUP
Google Sheet’s VLOOKUP returns undesired results when lookup values are not present in the source data. Combining INDEX with MATCH resolves the issue.
=INDEX(TabName!C:C,MATCH(B2,TabName!B:B,0))
will find the value in column C of tab TabName of the first row where the value in B2 matches a value in column B of TabName.