Description of formula:
Retrieves a single value from a list, data table or alias from app documents (if you want to get multiple values at once, use Lookup formula).

Search criteria
To find relevant data you should specify a query. The formula returns a value from a single row, which fulfill search criteria (if many rows meet criteria, the last one is returned). If you do not specify any query, formula returns data from the last record in the source. Query consists of conditions in the form of:
field_name operator value
field_name – a name of a column or an alias
operator – you can use one of the comparison operators: >, <, >=, <=, =, !=. Text values are compared in an alphabetical order.
value – you can enter a value directly, specify a cell address or calculate using a formula. The type of the value must be the same as field type.
You can join many conditions using and (&&) and or (||) operators. Query with and operator is satisfied only if both conditions are met. Query with or operator is satisfied when at least one condition is met. You can also use negation operator not (!) to reverse condition’s meaning. If you have more complex query, group conditions using brackets to define the order of evaluation.

Grouping data
You can also calculate a value for a group of records, using special formulas called aggregates. Pass selected aggregate as a third parameter, instead of returned field identifier. Aggregated value will be calculated from all records, which meet search criteria (if you do not specify a query – from all records in a data source). You can use one of the aggregates:
sum(field) – Sums values from the given field from all records in group
avg(field) – Calculates arithmetic mean of values from the given field from all records in group
count(field) – Counts records in group
min(field) – Gets the smallest value from the given field among all records in group
max(field) – Gets the largest value from the given field among all records in group
sumsqr(field) – Calculates the sum of squares of values from the given field from all records in group
lookupvalue(source, query, field)
(type: Identifier) Name of the list, data table or app, from which you want to retrieve data
(type: Logical) (optional parameter) Conditions which returned record should fulfilled
(type: Identifier) The value to return – a name of a column from list or data table, an alias from app or an aggregate
Returned type:
=lookupvalue(products, product_id=100, product_code)
=lookupvalue(sysuser, isactive and age > 18, min(age))
=lookupvalue(sales, region, avg(price))
=lookupvalue(sales, region = 'western' or region = 'northern', max(price))
A large number of calls of LookupValue formula can slow down your app. Especially beware of using it in the column of a Table section – the formula will be executed as many times, how many rows will appear in the section. If you want to get values from the same data source in multiple cells, you can do it more optimally: instead of retrieving single values by using LookupValue in every cell, get all required data at once. Use Lookup formula in an additional hidden section, and then use Find and Nth formulas to look for and reference appropriate values. In this way you will get exactly the same data, but you will save some time by doing one call instead of many.
Have more questions? Submit a request


Article is closed for comments.
Powered by Zendesk