Contains, ContainsAll, & ContainsOnly

Explore

Gallery

Contains, ContainsAll, & ContainsOnly

⁠
⁠
⁠

Formulas: Contains() , ContainsAll(), and ContainsOnly()

Category:

Lists(Array)⁠

⁠

Contains()

⁠

ContainsAll()

⁠

ContainsOnly()

Checks if a list contains any of the values of another list

Another way to say it...

Checks if a list contains all of the values of another list

Or, as one might say...

Checks if a list contains only the values of another list

Said differently...

Hey Coda, here’s a list for you. Does this list contain any of the values I provided?

Hey Coda, here’s a list for you. Now, does the list I provided contain all of these values?

Hey Coda, here’s a list for you. Now, does the list I provided contain only these values?

All three formulas take the same two parameters:

⁠

Contains(Value, Search)

These are the words, numbers, etc. that you want to look for in the list.

⁠

Value

Contains(Value, Search)

This is the list that you want to search for your value.

⁠
⁠
⁠

These formulas are generally used to check if a list contains a certain value and then uses the result to decide whether/how to act on that list.

In this way, the “contains” formulas are often used in conjunction with If(), SwitchIf(), and Filter(), and they can be helpful in lookup columns and filters.

To start with, let’s take a look at how these formulas check data from the list we have below:

LIST:

hamburgereurofill colorno yellingstationerysupplierthirstchapelbondscolorado

⁠

Contains()

We can use the Contains() formula to check if the list contains the word “euro.”

Euro?

true

⁠

The above list does contain the word “euro”, so the formula returns “true.”

ContainsAll()

We can use ContainsAll() to check if the list contains “euro,” “bonds,” and “yes.”

Euro, Bonds, Yes?

⁠

false

⁠

The list does not contain the word “yes”, so the formula returns “false.”

ContainsOnly()

We can use ContainsOnly() to check if the list list contains the word “chapel.”

Only Chapel?

⁠

false

⁠

The list does not only contain “chapel”, so the formula returns “false.”

Now let’s look at examples of how these formulas can be

used in your documents!

Let’s say we want to know which plants exist in a “mixed evergreen forest” within your

Plants Database⁠

. We can use either Contains() or ContainsOnly() to extract this information. Go ahead and play with the toggle to see the different values these two formulas pull:

⁠

ContainsOnly()

⁠

Contains()

⁠

Tree

Ecosystem

Common Holly

Mixed Evergreen Forest

Pink Honeysuckle

Mixed Evergreen Forest

No results from filter

⁠

This example illustrates the how each formula pulls info in different ways. Contains() is more inclusive and ContainsOnly() is more discriminatory.

Using the Contains() formula here is like saying:

“Hey Coda, please tell me each plant that can exist in a mixed green forest, even if they can exist somewhere else.”

Using the ContainsOnly() formula is like saying:

“Hey Coda, please tell me each plant that can only exist in a mixed green forest, nowhere else!”

Now let’s look at the same table, but with ContainsAll()

You can use the select control to select the plants that contain all of the ecosystems you select. Try finding the plants that are both in Riparian and Coastal Redwood areas.

Select Ecosystem:

None

⁠

Plants in Selected Ecosystem

Plants in Selected Ecosystem

⁠

Here’s the formula that controls the filter on the table above using ContainsOnly()

⁠

⁠
⁠
⁠

Important note: You will be using Contains() much more frequently than ContainsAll() and ContainsOnly() in real-world practice. Because of this, it will therefore be most beneficial for you to learn how to use Contains(), especially in your work on tables. This is where our activities will focus.