Operators are special symbols that perform specific operations on your controls or data. An operator is a character that represents an action. To use operators on Rules & Validations tab or to use operators to fetch numeric data from sheets using LOOKUP formula, you need to use the following operators. Operator are categorized into two category: 

Basic Operators

  • Equal To (=) - This operator is used to compare or retrieve data where the value needs to be exactly matched with the specified value. This operator can be used in a LOOKUP() function in a Sheet Lookup control as follows - 
LOOKUP([BaseSheet.unique_id,BaseSheet.email],BaseSheet.email="olag.gentil@house.gov")

This formula will fetch record(s) where email is olag.gentil@house.gov

  • Not Equal To (<>) - This operator is used to compare or retrieve all the data where the value does not match the specified value. This operator can be used in the following way in LOOKUP() function in Sheet Lookup control - 
LOOKUP([BaseSheet.unique_id,BaseSheet.name.first_name,BaseSheet.gender],BaseSheet.gender<>"Male")

The above formula will fetch all the records whose gender is not equal to Male.

  • Greater Than (>) - This operator is used to compare or retrieve data where the value is greater than the specified value. This operator is only applicable for number type controls, viz. Number, Decimal, Percent, etc. and date & time controls. For example, using this formula in Sheet Lookup control
LOOKUP([BaseSheet.unique_id,BaseSheet.averagescore],BaseSheet.averagescore>80)

The above formula will fetch all records having averagescore greater than 80.

  • Less Than (<) - This operator is used to compare or retrieve data where the value is less than the specified value. This operator is only applicable for number type controls, viz. Number, Decimal, Percent, etc. and date & time controls. For example, using this formula in Sheet Lookup control
LOOKUP([BaseSheet.unique_id,BaseSheet.averagescore],BaseSheet.averagescore<50)

The above formula will fetch all records with averagescore less than 50.

  • Greater Than Equal To (>=) - This operator is used to compare or retrieve data where the value is greater than or equal to  the specified value. This operator is only applicable for number type controls, viz. Number, Decimal, Percent, etc. and date & time controls.
  • Less Than Equal To (<=) - This operator is used to compare or retrieve data where the value is less than or equal to  the specified value. This operator is only applicable for number type controls, viz. Number, Decimal, Percent, etc. and date & time controls.
  • IsEmpty - This operators will evaluate if the control is empty. It will return true when the control is empty. This operator cannot be used in LOOKUP().
  • IsNotEmpty - This operator will evaluate if the control is not empty. It will return true when the controls is not empty. This operator cannot be used in LOOKUP().

Advance Operators

  • Contains ($cn) - This operators checks if a specified sequence of characters is present in a given control/column or not. This operator is used to search in text/string type controls viz. Text, Email, Name, Address, etc. For example, using this formula in Sheet Lookup control
LOOKUP([BaseSheet.unique_id,BaseSheet.department],BaseSheet.department $cn "Development")

The above formula will fetch all departments that contains the text "Development" as shown in the below image.

  • NotContains ($ncn) - This operator is used to return a value indicating whether a specified substring does not occur within the searched control/column. This operator is used to search in text/string type controls viz. Text, Email, Name, Address, etc. For example, using this formula in Sheet Lookup control
LOOKUP([BaseSheet.unique_id,BaseSheet.department],BaseSheet.department $ncn "Development")

The above formula will fetch all department that does not contain the text "Development".

  • StartsWith ($stw) - This operator is used to search a text/string that begins with the specified text/string. This operator is used to search in text/string type controls viz. Text, Email, Name, Address, etc. For example, using this formula in Sheet Lookup control
LOOKUP([BaseSheet.unique_id,BaseSheet.jobtitle],BaseSheet.jobtitle $stw "Acc")

The above formula will fetch all jobtitle that starts with or begins with the text "Acc", as shown below -

  • NotStartsWith ($nstw) - This operator works exactly opposite to StartsWith, where it is used to search a text/string that does not begin with the specified text/string. This operator is used to search in text/string type controls viz. Text, Email, Name, Address, etc. For example, using this formula in Sheet Lookup control
LOOKUP([BaseSheet.unique_id,BaseSheet.jobtitle],BaseSheet.jobtitle $nstw "Acc")

The above formula will fetch all jobtitles that does not begin with text "Acc".

  • EndsWith ($enw) - This operator is used to search a text/string that ends with the specified text/string. This operator is used to search in text/string type controls viz. Text, Email, Name, Address, etc. For example, using this formula in Sheet Lookup control
LOOKUP([BaseSheet.unique_id,BaseSheet.jobtitle],BaseSheet.jobtitle $enw "IV")

The above example will fetch all jobtitles that ends with "IV".

  • NotEndsWith ($nenw) - This operator works exactly opposite to EndsWith, where it is used to search a text/string that does not end with the specified text/string. This operator is used to search in text/string type controls viz. Text, Email, Name, Address, etc. For example, using this formula in Sheet Lookup control
LOOKUP([BaseSheet.unique_id,BaseSheet.jobtitle],BaseSheet.jobtitle $nenw "IV")

The above formula will fetch all jobtitle that does not ends with text "IV".

  • In ($in) - This operator is used to search a text/string where the value of the specified field is one of multiple specified values. The values are specified as a comma-delimited list, surrounded by parentheses. Using $in is equivalent to using multiple EQUALS (=) statements, but is shorter and more convenient. For example, using this formula in Sheet Lookup control
LOOKUP([BaseSheet.unique_id,BaseSheet.jobtitle],BaseSheet.jobtitle $in ["Teacher","Professor"])

The above formula, will fetch all the records that have jobtitle as Teacher or Professor as shown in the below image - 

  • Not In ($nin) - This operator is used to search for data where the value of the specified field is not one of multiple specified values. Using $nin is equivalent to using multiple NOT_EQUALS (!=) statements, but is shorter and more convenient. For example, using this formula in Sheet Lookup control
LOOKUP([BaseSheet.unique_id,BaseSheet.jobtitle],BaseSheet.jobtitle $nin ["Teacher","Professor"])

The above formula will fetch all records that do not have jobtitle as Teacher or Professor.

  • ContainsAny ($any) - This operator is used in Multiselect type controls viz. Multiselect, Checkbox, Keyword Box, etc. This operator is used to search the specified text/string in a given multiselect type control/column. For example, using this formula in Sheet Lookup control
LOOKUP([BaseSheet.unique_id,BaseSheet.skills],BaseSheet.skills $any ["Android SDK","TFS"])

The above formula will fetch all skills that has value either Android SDK or TFS or both Android SDK & TFS as shown in the below image - 

  • NotContainsAny ($nany) - This operator is used to search if the specified text/string is not present in the given control/column. This operator is used in Multiselect type controls viz. Multiselect, Checkbox, Keyword Box, etc. For example, using this formula in Sheet Lookup control
LOOKUP([BaseSheet.unique_id,BaseSheet.skills],BaseSheet.skills $nany ["Android SDK","TFS"])

This will fetch all skills that do not have Android SDK, or TFS or Android SDK & TFS.

  • ContainsAll ($all) - This operator is used to search if the given control/column contains all the values from specified multiselect control. This operator is used in Multiselect type controls viz. Multiselect, Checkbox, Keyword Box, etc. For example, using this formula in Sheet Lookup control
LOOKUP([BaseSheet.unique_id,BaseSheet.skills],BaseSheet.skills $all ["Android SDK","TFS"])

The above formula will fetch those record that have both the values "Android SDK" and "TFS"

  • NotContainsAll ($nall) - This operator is used to search and return all the records that does not contain all the values of the specified control/column. This operator is used in Multiselect type controls viz. Multiselect, Checkbox, Keyword Box, etc. For example, using this formula in Sheet Lookup control
LOOKUP([BaseSheet.unique_id,BaseSheet.skills],BaseSheet.skills $nall ["Android SDK","TFS"])

The above formula will fetch all records that does not have both values "Android SDK" and "TFS".

  • MinCount ($mncnt) - This operator is used to check if the given control/column has minimum count as specified in the criteria. This operator is used in Multiselect type controls viz. Multiselect, Checkbox, Keyword Box, etc. For example, using this formula in Sheet Lookup control
LOOKUP([BaseSheet.unique_id,BaseSheet.skills],BaseSheet.skills $mncnt 3)

The above formula will fetch those skills that have at least 3 values in a cell as shown in the below image.

  • MaxCount ($mxcnt) - This operator is used to check if the given control/column has the maximum count as specified in the criteria. This operator is used in Multiselect type controls viz. Multiselect, Checkbox, Keyword Box, etc. For example, using this formula in Sheet Lookup control
LOOKUP([BaseSheet.unique_id,BaseSheet.skills],BaseSheet.skills $mxcnt 1)

The above formula will fetch those skills that have at most 1 value in a cell as shown in the below image.

  • Count ($cnt) - This operator is used to return or check the count of the values in the given multiselect type control/column. This operator is used in Multiselect type controls viz. Multiselect, Checkbox, Keyword Box, etc. For example, using this formula in Sheet Lookup control
LOOKUP([BaseSheet.unique_id,BaseSheet.skills],BaseSheet.skills $cnt 2)

The above formula will fetch those skills that have only 2 values in a cell as shown in the below image.

  • Regex ($regex) - This operator is used to match a string/text or your data based on a regular expression. For example, using this formula in Sheet Lookup control
LOOKUP([BaseSheet.unique_id,BaseSheet.username],BaseSheet.username $regex "\d")

The above example will fetch those username that have number in it using the regular expression \d as shown in the below image.

Did this answer your question?