A sign-up app I built for a company event kept collecting the same email address two or three times, because people tapped Submit more than once. The fix was a single check before adding the address: does the collection already contain it? Power Apps has no Contains() function, which is what most people search for, but it has better tools for the job.
In this tutorial, I’ll show you how to check whether a collection contains a value with in, exactin, CountIf and LookUp, how to filter a collection, how to prevent duplicates, and where delegation limits can quietly give you wrong answers.
Quick answer: To check whether a collection has a value in one column, use "adelev@contoso.com" in colEmails.Email, which returns true or false and ignores case. Use exactin for a case-sensitive check. To count matches, use CountIf(colEmails, Email = txtEmail.Text). To filter, use Filter(colProducts, "Samsung" in Manufacturer.Value).
The tools that replace Contains()
| Check | Formula | Returns |
|---|---|---|
| Is this value in a column? | txtEmail.Text in colEmails.Email | true or false |
| Same, case-sensitive | txtEmail.Text exactin colEmails.Email | true or false |
| Does text contain a word? | "Laptop" in ThisRecord.'Product Name' | true or false |
| How many rows match? | CountIf(colEmails, Email = txtEmail.Text) | A number |
| Is there any match? | !IsEmpty(Filter(colEmails, Email = txtEmail.Text)) | true or false |
| Get the first match | LookUp(colEmails, Email = txtEmail.Text) | A record, or blank |
There’s one subtlety with in. When the right side is text, it’s a substring test, so "Sam" in "Samsung" is true. When the right side is a table, such as colEmails.Email, it tests membership: the value has to match a whole entry. That’s usually what you want for a “does it exist?” check.
Example 1: Filter a collection by a value
My SharePoint list, Product Details, has a Title, a Manufacturer choice column, a Price currency column and a PurchaseDate column. First, load it into a collection. Select the App object and add this to OnStart:
ClearCollect(colProducts, 'Product Details')
Show the collection in a data table by setting its Items to colProducts. Then add a button that collects only the Samsung products:
ClearCollect(colSamsung, Filter(colProducts, Manufacturer.Value = "Samsung"))Manufacturer is a choice column, so the value is in .Value. I use = for an exact match here. If you want a partial, case-insensitive match (so “sam” finds Samsung), use "sam" in Manufacturer.Value, and for case-sensitive matching, exactin. A second data table with Items set to colSamsung shows the result.
Example 2: Prevent duplicates in a collection
This is the sign-up scenario. A collection, colEmployeeEmail, has one column, Enter User Email. A text input (txt_Email) and a Submit button add new addresses. Set the button’s OnSelect to:
If(
CountIf(colEmployeeEmail, 'Enter User Email' = txt_Email.Text) > 0,
Notify("This email address is already on the list.", NotificationType.Warning),
Collect(colEmployeeEmail, {'Enter User Email': txt_Email.Text})
)
If the address is already there, a warning appears at the top of the screen; otherwise it’s added. The original version of this formula used CountRows(Filter(...)) > 0, which works too; CountIf is just shorter.
You can write the same check with membership: If(txt_Email.Text in colEmployeeEmail.'Enter User Email', Notify(...), Collect(...)). Adding the column name makes it check only that column, not the whole record.
People type addresses with different capitals, so it’s worth normalizing both sides, for example by collecting Lower(Trim(txt_Email.Text)) and comparing with the same expression.
Example 3: Set a variable if a value exists
Sometimes you only need a yes or no, for example to show a warning banner. This collection, Issuecol, comes from an Issue Tracker list with Product Issue and Product Name columns:
To check whether any issue is for a laptop or Outlook, use LookUp with a list of values, and turn the result into a true or false with IsBlank:
Set(varHasMatch, !IsBlank(LookUp(Issuecol, 'Product Name' in ["Laptop", "Outlook"])))LookUp returns the first matching record, or blank if there’s none, so varHasMatch is true when at least one issue matches. This version of the app used exactin inside LookUp and stored “Yes” or “No” text:
That works, but a real true or false is easier to use later, such as in a control’s Visible property. To test it in Studio, run OnStart again from the App object’s menu:
More ways to check a collection
Search a collection as the user types
For a search box, filter with in across the columns you want:
Filter(colProducts, txtSearch.Text in Title || txtSearch.Text in Manufacturer.Value)You can also use Search(colProducts, txtSearch.Text, Title) for text columns. Since 2024, Search takes column names without quotes, and it doesn’t search choice columns, so the Filter version is more flexible.
Check whether a record is already in a collection
ThisItem in colCart compares the whole record, so it fails if the copy in the collection has different columns or values. Compare a key instead:
If(ThisItem.ID in colCart.ID, "In cart", "Add")Find items that aren’t in a collection
Filter(colAllProducts, !(ThisRecord.ID in colCart.ID))This returns the products that haven’t been added yet, which is handy for “pick from the remaining items” screens.
Watch out for delegation
Checks on a collection are always complete, because the data is already in the app. The catch is loading it. ClearCollect from a SharePoint list isn’t delegable, so it only copies the first 500 items by default, or up to 2,000 if you raise the Data row limit in Settings > General. With a bigger list, your collection is silently incomplete, and “not found” might be wrong.
The same applies when you use these operators directly on a SharePoint list: in, exactin and Not aren’t delegable to SharePoint, while = on a text, number or choice value is. For large lists, filter with = or StartsWith before collecting. See how to fix Power Apps delegation warnings for more.
One more gotcha: IsBlank(colProducts) is false even when the collection is empty. Use IsEmpty(colProducts) to check for an empty collection.
These Power Apps collection tutorials may help:
- Power Apps Collection [Complete Guide]
- How to Filter Power Apps Collection?[Text/Number/Choice/Date/Person]
- How to Create a Power Apps Collection On OnStart Property?
- How to Remove an Item From a Power Apps Collection? [With Examples]
- Power Apps Table vs Collection
- Power Apps Delegation Warnings – How to Overcome
Frequently Asked Questions
Is there a Contains function in Power Apps?
No. Use the in operator, which is case-insensitive, or exactin, which is case-sensitive. Microsoft documents both in its Power Fx operators reference.
How do I check if a value exists in a Power Apps collection?
Use value in Collection.Column, which returns true or false, or CountIf(Collection, Column = value) > 0.
What’s the difference between in and exactin?
in ignores capitalization, and exactin doesn’t. Both do partial matching on text and whole-value matching on a table column.
How do I prevent duplicates in a collection?
Before Collect, check with CountIf or in, and show a Notify message if the value is already there.
Why does my collection check miss items?
The collection may be incomplete. ClearCollect from SharePoint only loads 500 items by default, up to 2,000 with the Data row limit setting.
Preeti Sahu is a Microsoft MVP for Power Apps with over six years of experience working with SharePoint Online and the Power Platform. She is the co-author of the book Microsoft Power Platform: A Deep Dive. As a Power Platform developer, she has built various tools with Power Apps and Power Automate, and she makes Microsoft 365 videos for YouTube.