Useful Google Sheets Queries

Queries for pulling in associated data in different sheets:

Check a cell for the presence of a number of different string possibilities contained in an array. Then, instead of the default “true” or “false” output, out “-10” or “0”:

=ArrayFormula(if(REGEXMATCH(D721, JOIN("|",{"com","net","org"})),-10,0))

Returns “TRUE” or “FALSE” in a new cell if cell being queried is unique for the column it is in. In the example, “F10” is the output column and “F” is the column to query:

Unify values:

Extract just domain name:
=regexextract(D:D, “://w*\.*([^/]+)”)

Add values:

Check if values in a certain cell contains a value. In this case, “.asp” but also good for checking and sorting lists by certain characteristics:

Calling a cell or cells in another Spreadsheet:


Calling a cell or sells in same Spreadsheet using a named range or worksheet name:

=query(ContentNAMEDRange,"SELECT A, C, E, F WHERE D='"&F2&"'" )

To add:

  • Query cell ranges where value == 1
  • Import range
  • Query cell values where cell value on origin sheet equals value in new sheet, like domain