The LookUp function in PowerApps: Everything you need to know

 

The LookUp function in PowerApps: Everything you need to know

I'll show you how to use the Lookup function in Power Apps to search for specific data records. I use the Lookup function in almost every app I use.

What is the LookUp function?

The Lookup function in Power Apps returns the first record that meets one or more conditions. You can use this function to search for specific values ​​in various data sources, such as SharePoint lists, Dataverse tables, Excel spreadsheets, or collections . ( See here for more details on collections .)

Step 1: App Development

Start with an app that's already connected to a data source (e.g., a SharePoint list).  Add a label field (1) or an edit form (2) to display the result of the lookup function . Use a label field if you want to use individual values ​​from the lookup function. An edit form is well-suited for displaying an entire data set.

Step 2: LookUp Function

The LookUp function can process 3 arguments. The source and the condition are required; the result is optional.

You specify the table to be searched as the source . In this example, I'm using the SharePoint list "Shopping Products".

The condition requires a Boolean result (true or false). In this example, I'm using the formula ID = 2. This means we should search for the first record whose ID is 2.

For the "Result" argument, I'm adding "Product" to the label field to get the product name. We don't need to specify any data for the result in the edit form, as the entire data set can be displayed there.  

Label field
Text = LookUp('Shopping Products';ID=2;Product)

Edit form
Item = LookUp('Shopping Products';ID=2)

Step 3: Working with the LookUp function

The Lookup function allows you to compare different data types such as numbers, text, dates, and even more complex columns. Here are some practical examples:

1.  Simple comparison : Compare the ID column with a specific value to find the corresponding record.
   

   Label field
   Text = LookUp('Shopping Products';ID=2;Product)

2. More complex comparisons : Check a selection or person column. For example, you can check the status of an order or return the first record created by a specific user.
   

   Address selection column
   Label field
   Text = LookUp('Shopping Products';Status.Value="In Stock";Product)  

   Address the person column:
   Label field
   Text =  LookUp('Shopping Products';'Created by'.DisplayName="Kai Weissmann";Product)  

Step 3: Returning multiple fields

A major advantage of the LookUp function is that you can return multiple fields simultaneously . This saves resources and improves your app's performance. Use the third optional argument , for example, to multiply the stock level and price of a product to calculate the total value of your inventory.

Label field
Text = LookUp('Shopping Products';ID=2;Stock level*Price)

Step 4: Check multiple conditions

You can further improve your app's performance by combining multiple conditions in the LookUp function. Use logical operators like **AND** and **OR** to create precise search queries. For example, you can search for a product with a stock level greater than 10 and a price greater than 1000.

Label field
Text = LookUp('Shopping Products';Stock>10AndPrice>1000;Product) 

BONUS: Working with empty values

If the LookUp function does not find a record, it returns a blank value. The IsBlank function returns false if a record was found and true if no record was found.

Label field
Text =
If(
IsBlank(LookUp('Shopping Products';            Stock>10AndPrice>2000;            Product));"No record found";LookUp('Shopping Products';        Stock>10AndPrice>2000;        Product))