LookUp function in PowerApps allows you to find data from a related data source or table that satisfies the condition or formula given in the lookup function. It returns a single record or a single value.
Lookup(Source, Column, Criteria)
- Source: This is the data source or table from which you want to retrieve data.
- Column: Specifies the column from which you want to retrieve data.
- Criteria: The value that you want to match against the data in the source.
Let’s look at an example of using LookUp function.
Using LookUp function to get single record
Here, we are using a SharPoint list named EmpInfo. It contains employee’s data.
LookUp function to get the details of an employee working for Finance department.
To see the employee name, just add a Text label control and write the below formula on Text property of Text label control.
LookUp( EmpInfo, Department.Value = "Finance" ).Name
You can see this formula, returns the first record that match the condition.
You can also write the above formula like this.
LookUp( EmpInfo, Department.Value = "Finance", Name )
In SharePint list there are two employees in the Finance department but it returns Sumit Joshi, that is the first record in SharePoint list.
Let’s understand the above formula. Here EmpInfo is the data source name which is SharePoint list named EmpInfo.
Department.Value is the formula, to match for department name Finance in records.
Note that: Data type of department is the choice column in SharePoint that’s why we have used Department.Value to get the value.
Adding multiple conditions to LookUp function
You can also add multiple conditions to LookUp function. It returns a single record or a single value that satisfies the condition.
Let’s get the employee name where Department name is Finance and Country is USA.
LookUp( EmpInfo, Department.Value = "Finance" && Country = "USA", Name )
You can see, it returns David Jr. that is the first record which satisfy the LookUp condition.
Using Lookup to get details from another SharePoint list
As you can see, here we have a gallery which display employee details. It is using SharePoint list named EmpInfo.
Suppose, you want to display employee’s skills. But Skill information is not available in EmpInfo data source.
There is one more SharePoint list named EmpSkills which contains Skills details of each employee. So, to fetch display skills, we need to lookup this list.
Lets take a look at SharePoint list EmpSkills and you can see it contains the Skills details for each employee.
Let’s add one Text label control in gallery to display employee Skills.
Now, to get the employee skills we need to apply LookUp on EmpID column as this column is common in both list EmpInfo and EmpSkills.
Now, set Text property of Text label control to below formula.
LookUp( EmpSkills, EmpID = ThisItem.EmpID, Skills )
Lets understand the above formula, EmpSkills is a data source contains the skills details for each employee.
ThisItem.EmpID represent the current selected item in gallery and gallery is connected to data source EmpInfo.
So, the condition EmpID = ThisItem.EmpID checks, if EmpID of data source EmpSkills is equal to EmpID of data source EmpInfo then return the Skills from EmpSkills list.
Now you can see, it displays the skills of each employees.