PowerApps LookUp vs Filter vs Search Functions by Examples

PowerApps LookUp function, PowerApps Filter function and PowerApps Search function are confusing in the beginning. The three functions do filter records, but there are small differences. This article explains when to use which function, how there are used and shows the usage of the functions in simple examples.

We describe each function detail in this article. In case you just want to know, when to use the PowerApps LookUp function, when to use PowerApps Filter function and when to use the PowerApps Search function. Here is th answer in short:

LookUp Function

The LookUp function in PowerApps filters for the first item for a filter condition.

Syntax

LookUp(Table*, Formula [, ReductionFormula ] )

Input parameters

• Table* (mandatory): The collection where you want to lookup an item.
• Formula (mandatory): The condition the looked up item should match.
• ReductionFormula (optional): In case you do not need all data of the looked up item, you can define here what you exactly need.

Return value

• Without Reduction Formula it will be a single record of the given table
• With Reduction Formula it can be everything the formula defines (string, number, ..)
• Returns blank, when no record is found

LookUp Function Examples

To demonstrate the LookUp function we create some test data:

Name	Age	Company
John Doe	45	Contenso
Maria Smith	23	Contenso
Mike Miller	33	Microsoft

This is how the test data can be created via PowerApps.

ClearCollect(
    Contacts,
    {
        Name: "John Doe",
        Age: 45,
        Company: "Contenso"
    },
    {
        Name: "Maria Smith",
        Age: 23,
        Company: "Contenso"
    },
    {
        Name: "Mike Miller",
        Age: 33,
        Company: "Microsoft"
    }
);

Example: LookUp record by column value

To get the single entry of John Doe from our sample records, you can do the following.

LookUp(Contacts,Name = "John Doe");
/*
 Returns  
   {
        Name: "John Doe",
        Age: 45,
        Company: "Contenso"
    }

*/

But what if there would be multiple records matching the lookup condition? LookUp will return the first record, that matches the condition. For instance, there are two entries in our table, that work for the company Contenso (John & Maria). Since we defined John before Maria, the following example will return John.

LookUp(Contacts,Company= "Contenso");
/*
 Returns  
   {
        Name: "John Doe",
        Age: 45,
        Company: "Contenso"
    }

*/

Example: LookUp record by column value with reduction formula

The reduction formula let’s you define, which data you need of the record that matches the lookup.

LookUp(Contacts,Name = "John Doe",Age);
// Returns 45

LookUp(Contacts,Name = "John Doe",Name & " (" & Company & ")");
// Returns John Doe (Contenso)

Example: LookUp with no matching record

What if there is no record matching the condition? A blank value will be returned.

IsBlank(LookUp(Contacts,Name = "Darth Vader"))
// Returns true

Filter Function

The PowerApps Filter function filters all items of a collection matching the given condition(s).

Syntax

Filter(Table*, Formula1 [, Formula2, ... ] )

Input Parameters

• Table (mandatory): The table you want to filter.
• Formula1 (mandatory): The filter condition to filter the records.
• Formula2 (optional): More filter conditions (can be multiple).

Return Value

• Returns a collection of items that match the condition(s)
• -Is empty in case no item matches the conditions

PowerApps Filter Function Examples

ClearCollect(Numbers, [1,2,3,4]);

Filter(Numbers, Value>1);
// Returns 2,3,4

Filter(Contacts,Age>30);
// Returns a collection with the items of John and Mike 
// (see creation of Contacts at the bginning of ths article)

Filter(Numbers, Value>1,Value<4)
// Returns 2,3

Filter(Numbers, Value>1,Value<4,Value <> 3 )
// REturns 2

IsEmpty(Filter(Numbers, Value>4 ));
// Returns true

Search Function

The PowerApps Search functions filters a table by checking if a defined search string is contained within one or multiple columns with ignoring case sensivity.

Syntax

Search(Table*, SearchString, Column1 [, Column2, ... ] )

Input Parameters

• Table (mandatory): The table in which you are search items.
• SearchString (mandatory): Text that should be contained in the column(s) value. (Empty search string returns all recotds)
• Column1 (mandatory): The table column where the value of the column gets checkd if it cotains the search string.
• Column2 (optional): Additonal columns that are being searched (can be multiple).

Return Value

• Returns a collection of items that match the search.
• Returns an empty collection in case the search does not find anything.

Examples PowerApps Search function

ClearCollect(Names, ["Ada","Aaron","Sandy","Andy"]);
Search(Names,"an","Value")
// Returns Sandy and Andy

Search(Contacts,"n","Name","Company");
// Returns John Doe (Contenso) & Maria Smith (Contenso) 
// (see creation of Contacts at the bginning of ths article)


IsEmpty(Search(Names, "Sally","Value"))
// Returns true