How to use the lookup function in Power Apps
Table of Contents
What is the Lookup function in Power Apps?
Think about how you use the VLOOKUP or XLOOKUP formulas in Microsoft Excel – there are two tables in the file and one table has some column that the other one doesn’t, so a VLOOKUP column is required to bring the information from that other table.
It’s quite similar in Power Apps: The LookUp function in Power Apps is used to find the first record in a data source that matches a specified condition. It’s commonly used to retrieve a specific value or record from a table or list, based on a filter or criteria you define.
Syntax and parameters of the LookUp function
Power Fx syntax and parameters
Based on Microsoft documentation, the LookUp function uses the following syntax:
LookUp(Table*, Formula [, *ReductionFormula* ] )
- Table – Table to search.
- Formula – The formula by which each record of the table is evaluated. It’s usually a condition that needs to be met for a record to be part of the result. You can reference columns within the table. In the UI, the syntax is shown as condition above the function box.
- ReductionFormula – If this parameter is left blank, then Power Apps will bring all the available columns in the record. If a specific column or value needs to be retrieved, then this column name can be written in this parameter and Power Apps will reduce the results to only this column’s value.
Table and Formula are required parameters.
ReductionFormula is optional.
We can also write LookUp(Table*, Formula) with no additional parameters and just write a “.” after the closing parenthesis to indicate the column we need to calculate.
LookUp vs. filter vs. search: quick comparison (Table)
There a few functions in Power Apps that can be used to evaluate a condition and return one or multiple records that meet this condition – LookUp, Filter and Search. Let’s quickly go over these three functions to understand the difference.
LookUp | Filter | Search | |
When to use | To find a single record that matches one or more criteria in one or more columns. | To find a one or more records that matches one or more criteria in one or more columns. | To find a one or more records that contains a text string in one of the columns |
Result returned | A single record with the same columnas as the original table | Table that contains the same columns as the original table and the records that match the criteria | Same as filter |
What happens if no results meet the criteria | Returns blank | Returns an empty table | Returns an empty table |
Power Apps controls | Usually used to display values in forms, text inputs or text labels.
Very often also used to store the value of a whole record to be used in different controles or screens in the app. | Usually used in galleries or data tables. | Usually used in galleries or data tables. |
Step-by-step guide: How to use the Lookup function
Step 1 – Connect your data source
First, let’s bring our SharePoint list into the app
Once connected, it should look as follows:
Step 2 – Add a Lookup formula in your app
Let’s suppose we want to show in a text control the name of the manager of each department.
Let’s add two text label controls:
In the Formula bar, let’s write the LookUp function:
"Finance - "& LookUp(Departments,Title="Finance",Manager.DisplayName)
- Table/List to search
- Criteria to match
- Result to display
"Finance - "& LookUp(Departments,Title="Finance").Manager.DisplayName
The Marketing expression would be:
"Marketing - "& LookUp(Departments,Title="Marketing",Manager.DisplayName)
What the function does is searching in the Departments list, filtering by the condition we’re setting and finally returning the Manager name.
Common issues with the Lookup function (and how to fix them)
Lookup not returning expected values
If LookUp is not returning the value you expect, there could be many reasons but here are a few things to check in the expression:
- Data Formatting: Double-check t data types. For example, comparing a text to a number can result in no match.
// This will NOT work:
LookUp(Departments,ID="1",Manager.DisplayName)
//ID is a number type column, while “1” is a text string
//This will work:
LookUp(Departments,ID=1,Manager.DisplayName)
//Comparing numbers with numbers is the right approach
- Check for typos: When applying conditions with exact match logic, make sure the values actually match.
Delegation Warnings in Power Apps
Delegation refers to Power Apps delegating data operations to the data source rather than pulling all records into the app. For example, when we use the Filter function, Power Apps sends the filters we set to SharePoint for SharePoint to perform the operation, and Power Apps will only display the results rather than performing the task inside Power Apps.
If the operation is not delegated, it means that Power Apps first pulls the records from the datasource, and then performs the operation on top of the records that were pulled. The issue is that Power Apps has a limit of the number of records it can pull at a time – it can go from 1 to 2000.
Depending on the data source, some expressions might allow delegation and some others might not. It’s important to keep an eye on the accuracy of the results returned by Power Apps to make sure delegation issues are not happening.
In most cases, Power Apps will warn the developer of any operation that may not work in a larger dataset (ie – the operation cannot be delegated). If this is the case, make sure to only use delegable expressions. For example, “equals” to comparisons on indexed columns work best.
Real business use cases for the Lookup function
Managing Employee Records
In HR or Admin apps, LookUp can pull key details like job title, department, or manager when an employee is selected from a list. For example, selecting an employee in a ComboBox could automatically display their office location and contact information.
Retrieving Related Customer Data in Sales Apps
LookUp can fetch data like customer type, region, or outstanding balance based on the selected account or client.
Displaying Dynamic Pricing or Product Details
In retail or inventory apps, users can select a product and by using the LookUp function instantly view its price, stock availability, or category.
Examples of LookUp function
Bring a whole record with columns or bring a single value
There are two different ways in which the LookUp function can be used:
1. Bring a single value – ie, a person’s name or row ID. For this, any of these expressions can be used
//option 1
LookUp(Departments,Title="Marketing",Manager.DisplayName)
//option 2
LookUp(Departments,Title="Marketing").Manager.DisplayName
2. Bring a record with all the columns – ie, the first record that matches the criteria with ALL the columns for that record. For this, the following expression can be used
LookUp(Departments,Title="Marketing")
Bringing the whole record can end up being really useful if for example we will use different columns of the resulting record in different places of the app.
Define a variable based on a Lookup result
The LookUp function can be used along with the Set function to save the results of the lookup operation in a variable
Set(
varLookUpDisplayName,
LookUp( Departments, Title = "Marketing", Manager.DisplayName)
)
//Saving the Manager’s name into the variable varLookUpDisplayName
Set(
varLookUpRecord,
LookUp( Departments, Title = "Marketing")
)
//Saving the record with all its columns (title, ID, approvers, employees, manager) into the variable varLookUpRecord.
Bring the most recently created record
We can add a sort expression to sort in descending mode the table or list “Departments” to bring the record with the highest value for column ID.
In SharePoint, when new records are created, they get and ID automatically assigned by adding +1 to the previous record created, so the highest ID value will always be the most recent value.
Set(
varLookUp,
LookUp(
Sort(
Departments,
ID,
SortOrder.Descending
),
Title = "Marketing",
Manager.DisplayName
)
)
Check if value is blank
Once we have LookUp results stored in variables, we can evaluate if there is any result that matches the condition by using the IsBlank function. This will return TRUE if the LookUp is Blank, or false if there is any resulting record.
IsBlank(varLookUp)
We can use this IsBlank inside an “If” statement to display different texts or to change the display mode of a control:
//show text
If(
IsBlank(varLookUp),
"No matching department",
"There is matching department"
)
//define Display mode
If(
IsBlank(varLookUp),
DisplayMode.View,
DisplayMode.Edit
)
Best practices for using Lookup in Power Apps
When displaying data in a gallery, keep in mind that if a LookUp function is called inside the gallery, this operation will execute for all the records in the gallery. When possible, create related columns (by using lookup columns in Dataverse or SharePoint), to bring additional columns as part of the original table or list, instead of having to calculate new columns on demand on the app by using LookUp on all the rows in a gallery.
Performance tips for large datasets
Add Index Columns in the Data Source
SharePoint has options to create Index columns for lists, make sure to always define index columns when SharePoint lists are connected to a Power Apps app. Also, for SQL databases, index columns can be defined and it’s recommended to do so as well.
When possible, cache Data with Collections or Formulas (Cautiously)
If the number of rows in the table or list is small enough, it can be possible to save a “local” copy of the table into a collection or a formula that can be referenced in the application when performing lookups. This can help in avoiding sending too many “LookUp” requests against the data source and instead querying the local collection or formula.
For the example we were working on, the departments table only had two rows, so instead of performing lookups against the SharePoint list, the Departments table can be stored as a Named formula and perform the lookup against this formula.
Once the formula is created, it can be referenced from anywhere in the app and it can be used with LookUp functions to extract the very same data as if we were querying the SharePoint list.
Save records in a variable and avoid Repeating LookUps
If the columns of a record will be used in different places of the application, do not create a lookup everytime the columns are needed, instead, create a variable that stores the whole record with all its columns and use this variable when needed. Bringing the whole record can end up being really useful if for example we will use different columns of the resulting record in different places of the app.
//AVOID this:
LookUp(frDepartments,Title="Marketing",Manager.DisplayName)
//Do this instead:
Set(varDepartment,LookUp(frDepartments,Title="Marketing")
And call varDepartment every time it’s needed:
varDepartment.ID
varDepartment.Title
varDepartment.Manager.DisplayName
Frequently Asked Questions (FAQs)
Can I use Lookup to retrieve multiple rows?
No, the LookUp function can extract only one record. When the requirement is to bring more than one, use the Filter or Search functions.
How do I use Lookup with a ComboBox?
It has a similar structure to what was shown above in this post. The only difference is that instead of using “Marketing” as a hard-coded text, the selected value of the combobox:
LookUp(DataSource, ID = ComboBox.Selected.ID)
Can Lookup be used with variables?
Yes, the result of a LookUp expression can be stored as a variable.
Explore how PowerGI can help you build smarter Power Apps
The LookUp function in Power Apps can be really useful and powerful, but using it without following the best practices can also lead to undesired performance issues. If you’d like some help with LookUp or any other Power Fx, contact us, we’re happy to start a conversation and see how we can help!
