Popular sheets formulas

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:


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.


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.

Leave a Reply