HAS Function

Search for an exact match of a value, including multi-contact or multi-select dropdown column cells or ranges. Returns true if found, false if not found.
Sample Usage
=IF(HAS([Day of Week]: [Day of Week], "Monday"), "yes", “no”)
Syntax
HAS(
  • search_range
  • criterion
)
  • search_range
    The cell or cell range to search within.
  • criterion
    The value you want to find.
Usage Notes
  • The HAS function is designed to work with multi-select dropdown and multi-contact columns. It won’t cause an error if used with other column types, but it will only return “true” if the criterion exactly matches the contents of the specified cell.

    In the example above, if [Day of Week]1 is in a Text/Number column, HAS will return “true” if [Day of Week]1 = “Monday” but will return “false” if [Day of Week]1 = “Monday Tuesday.”
  • The HAS function is distinct from CONTAINS because it searches for specific values where CONTAINS searches for strings or characters containing the criteria.

    For example, using the CONTAINS function to search for “app” (in any column type) will return both “apps” and “apples”. To search specifically for “apples”, (in any column type, including multi-select or multi-contact columns) use the HAS function.
Examples

This example references the following sheet information:

Row #

Clothing Item

Units Sold

Sold Date

Status

1

T-Shirt

78

02/12/19 10:23 PM

Green

2

Pants

42

02/15/19 1:55 AM

Red

3

Jacket

217

02/20/19 2:45 PM

Yellow

 

Given the table above, here are some examples of using CONTAINS in a sheet:

 

Formula

Description

Result

=SUMIF([Clothing Item]:[Clothing Item], HAS(@cell, "T-Shirt"), [Units Sold]:[Units Sold])

Sum the cell values in the“Units Sold”column if the adjacent cell in the “Clothing Item” column of the same row has the exact matching value of “T-Shirt”

 

Row 1 meets this criteria.

78

=COUNTIFS([Sold Date]:[Sold Date], HAS(@cell, "1:55 PM"))

Count the cell values in the “Sold Date” column that has the exact matching value of value “02/15/19 1:55 AM”.

 

Row 2 meets the criteria.

1

=IF(HAS([Clothing Item]:[Clothing Item], "Jacket"), "True", "False")

If the Clothing Item column has the exact matching value of “Jacket” produce the value “True”.

 

Row 3 meets this criteria.

True