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
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:
. 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
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()
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.
Practice:Use this lesson’s formulas to check provided values against the list.
Done
Learn to use Contains() to filter a view of a table