When building applications in Power Apps, you'll frequently need to retrieve data from collections, SharePoint lists, Dataverse tables, or other data sources. Two of the most commonly used functions for this purpose are Lookup() and Filter().

Although both functions help you find records, they serve different purposes and return different types of results. Understanding when to use each function will help you write more efficient and reliable Power Apps formulas.

In this article, we'll break down the differences using a simple example.

Step 1: Create a Sample Collection

For demonstration purposes, create the following collection in your App OnStart property or Screen OnVisible property:

ClearCollect(
    colProjects,
    [
        {ProjectID: 101, ProjectName: "Website Redesign", Status: "Active"},
        {ProjectID: 102, ProjectName: "Mobile App", Status: "Completed"},
        {ProjectID: 103, ProjectName: "CRM Upgrade", Status: "Active"},
        {ProjectID: 104, ProjectName: "HR Portal", Status: "Planning"}
    ]
)

This creates a collection containing four projects with different statuses.

The Core Difference

The easiest way to remember the difference is:

• Lookup() returns a single record.

• Filter() returns one or more matching records.

Think of it this way:

Need one record? → Use Lookup()

Need multiple records? → Use Filter()

What is Lookup()?

The Lookup() function searches a table and returns the first record that matches the specified condition.

Once a match is found, Power Apps stops searching and returns that record.

Syntax

Lookup(DataSource, Condition)

Example

Lookup(colProjects, Status = "Active")

Result

{
    ProjectID: 101,
    ProjectName: "Website Redesign",
    Status: "Active"
}

Even though there are two active projects, only the first matching record is returned.

When Should You Use Lookup()?

Lookup() is ideal when:

• Retrieving details for a specific record.

• Searching by a unique identifier such as Project ID.

• Displaying information on a details screen.

• Fetching a single value such as a project name, email address, or status.

Example

Get the project name for Project ID 103:

Lookup(colProjects, ProjectID = 103).ProjectName

Result:

"CRM Upgrade"

What is Filter()?

The Filter() function searches a table and returns all records that meet the specified condition.

Unlike Lookup(), it does not stop after finding the first match.

Syntax

Filter(DataSource, Condition)

Example

Filter(colProjects, Status = "Active")

Result

[
    {ProjectID: 101, ProjectName: "Website Redesign", Status: "Active"},
    {ProjectID: 103, ProjectName: "CRM Upgrade", Status: "Active"}
]

Both active projects are returned because they satisfy the condition.

When Should You Use Filter()?

Filter() is useful when:

• Populating galleries with multiple records.

• Creating search functionality.

• Filtering data based on user selections.

• Displaying all records that meet a condition.

Example

Show all completed projects:

Filter(colProjects, Status = "Completed")

This returns every project whose status is marked as Completed.

Comparison Table

Common Mistake to Avoid

A common mistake among new Power Apps developers is using Lookup() to populate a gallery.

Since galleries expect a table of records, Filter() should be used instead.

Incorrect

Lookup(colProjects, Status = "Active")

Correct

Filter(colProjects, Status = "Active")

Likewise, if you only need the details of a selected project, using Filter() may return unnecessary records. In that case, Lookup() is the better option.

Final Thoughts

Both Lookup() and Filter() are essential functions for working with data in Power Apps.

• Use Lookup() when you need a single record or value.

• Use Filter() when you need all records that match a condition.

While this article used a simple project management collection, the same concepts apply when working with SharePoint lists, Dataverse tables, SQL databases, and other data sources.

Choosing the right function not only improves app performance but also makes your formulas easier to understand and maintain.