Article to understand how to use the LOOKUP() Function.
The LOOKUP function is used to fetch data from the sheet which can be based on certain criteria. The fetched data is sorted in ascending order and provides a match. The most common use is to bind the form control to the columns to fetch and display data.
To fetch data from any sheet and bind it to a control, you need to define the LOOKUP function in the control’s formula box. The LOOKUP function works by looking up values in a given range of rows and columns or even from an array.
Syntax
LOOKUP([Sheetname.columnname], filter, sorting, order)
Where,
- Sheetname : is the sheet from where the data is to be fetched;
- Columnname : is the column whose data is to be fetched.
The parameters mentioned below are optional:
- Filter : is the filter format to fetch data from the column. There are three components as Columns to be filtered, filter, and operator .
- Sorting: is used to specify the column to sort the data on;
- Order: the result set is order on the basis of the column specified in ascending (“ASC”) or descending (“DESC”) order.
The function returns the list of rows based on the filter criteria with the column details as specified using the filters and data type as string, numeric, date, and others as well.
Let’s understand the LOOKUP function with a few examples. In the examples below, we are assuming that the city, state, and zipcode are the form controls for which the data will be fetched or whose input data will be used for different filter examples
Simple LOOKUP function to fetch data from different columns
Let’s take a simple example where you are getting all the rows from the sheet. In this example, let us use the Citymaster_docs sheet.
The formula will be built as follows:
LOOKUP([Citymaster_docs.City,Citymaster_docs.State,Citymaster_docs.Postal_code,Citymaster_docs.Abbreviation])
If you see the resultset the data for all the selected columns is displayed.
Adding a filter using operators for LOOKUP function
You can also define various filters to retrieve data based on a condition. You can use the AND, OR, NOT and other operators like MaxCount, MinCount, Contains, and so on listed here under the LOOKUP filter Truth Table here.
Now let us assume that we need to fetch data with a filter that the city rows are fetched where the state is the one specified.
So let us build the formula as follows:
LOOKUP([Citymaster_docs.City], Citymaster_docs.State = “Alabama”))
Let us take another sheet - Prod_doctest - here to understand a few more scenarios.
Now let us have a look at an example to fetch data that satisfies multiple conditions. Say for example you want to fetch the Product name from Prod_doctest sheet, whose Product type is ‘Apparels’ and Quantity is more than or equal to 5. The formula will be built using the AND operator as follows:
The formula will be built as follows:
LOOKUP([Prod_doctest.Prod_name] , AND(Prod_doctest.Prod_type=“Apparels”, Prod_doctest.Quantity>=5)))
Let’s take another example using the OR operator. Say for example you want to fetch the Product name from Prod_doctest sheet, whose Product type is ‘Apparels’ or where the Quantity is more than or equal to 7. The formula will be built using the OR operator as follows:
Formula:
LOOKUP([Prod_doctest.Prod_name] , OR(Prod_doctest.Quantity>= 7, Prod_doctest.Prod_type=“Apparels”))
Sorting the data fetched with LOOKUP()
When you are using the LOOKUP function to fetch data you might want to sort the data in an ascending or descending order based on one of the columns.
The formula is built as follows:
LOOKUP([Prod_doctest.Prod_name,Prod_doctest.Quantity,Prod_doctest.Price,Prod_doctest.Prod_type],"",Prod_doctest.Prod_name,“ASC”)
where the data will be sorted in Ascending order based on the Prod_name column.
Thus the LOOKUP function can be used to fetch data as per condition and sorted in an ascending or descending order.