Gallery
Contains, ContainsAll, & ContainsOnly
Share
Explore

icon picker
Contains, ContainsAll, & ContainsOnly

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

container-truck.svg
Contains()

list.svg
ContainsAll()

one-finger.svg
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:

search.svg
Search

Contains(Value, Search)

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

exposure-value.svg
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 . 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()

1
2
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
2
Here’s the formula that controls the filter on the table above using ContainsOnly()
image.png

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.


Done
Done
Done


Share
 
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.