LOOKUP()

Returns the value from the column of the matching row of a table or slice. It returns only a single column value, even if several rows match.

Sample usage

LOOKUP("Bob's Burgers", "Restaurants", "Name", "Phone") returns the value of the Phone column from a row in the Restaurants data set with a Name column value that matches Bob's Burgers. Equivalent to ANY(SELECT(Restaurants[Phone], ([Name] = "Bob's Burgers"))). See also: ANY(), SELECT()

LOOKUP([_THISROW], "Managers", "Location", "Name") from a location row, gets the name of a manager whose assigned location is the one identified by the current row. Equivalent to ANY(SELECT(Managers[Name], ([Location] = [_THISROW]))).

LOOKUP([_THISROW].[Order ID], "Orders", "Order ID", "Order Date") from an order detail row, gets the order date from the parent order. Equivalent to ANY(SELECT(Orders[Order Date], ([Order ID] = [_THISROW].[Order ID]))) or [Order ID].[Order Date].

Syntax

LOOKUP(value, dataset, column, return-column)

• value - The value to match (as with the = operator) in the given data set and column. The value must be of a type suitable for comparison with the data set column. If value is an expression, it is evaluated from the perspective of the lookup data set (dataset). To reference columns of the current context, dereference _THISROW.  See also: SELECT()
• dataset - The name of the table or slice (the "data set") in which to search as literal text value, optionally enclosed in quotes to avoid confusion with reserved words. The argument may not be an expression.
• column - The name of the column in which to search as literal text value, optionally enclosed in quotes to avoid confusion with reserved words. The argument may not be an expression.
• return-column - The name of the column whose value should be returned as literal text value, optionally enclosed in quotes to avoid confusion with reserved words. The argument may not be an expression.

Notes

The data set and column name arguments (dataset, column, return-column) must be simple text values; they may not be column references or more complex expressions. They should be enclosed in quotes to avoid confusion should any match internal names used by AppSheet itself. For instance, an unquoted column name of Date will produce an error.

Troubleshoot

LOOKUP() is effectively a wrapper to the SELECT() function. As with SELECT(), any column references are interpreted from the perspective of the data set being searched, not that of the data set from which the expression is run.

For example, consider this attempt from an order detail row to get the parent order's order date:

LOOKUP([Order ID], "Orders", "Order ID", "Order Date")

While this would produce a result, the result would likely be incorrect. Consider the equivalent SELECT() expression:

ANY(SELECT(Orders[Order Date], ([Order ID] = [Order ID])))

The comparison matches the order's Order ID against itself, which will always be TRUE. Therefore, the SELECT() matches all orders, not just the parent of the order detail row as intended.

To reference the column values of the row from which LOOKUP() is used, dereference _THISROW. For example:

LOOKUP([_THISROW].[Order ID], "Orders", "Order ID", "Order Date")

The equivalent SELECT() expression:

ANY(SELECT(Orders[Order Date], ([_THISROW].[Order ID] = [Order ID])))

See also: ANY(), SELECT()

See also

Lookup functionfeature sample

FILTER()

MAXROW()

MINROW()

REF_ROWS()

SELECT()