LOOKUP

Retrieves values from a data source.

Note: Formulas containing LOOKUP() functions are not automatically changed when you modify the underlying Data Source.  You must manually update LOOKUP() calls to match changes you make to the Data Source.  For example, a single Data Source can be used to retrieve and hold multiple column values from a single SQL query, a significant performance gain over fetching individual values from multiple SQL queries.  In this case, the LOOKUP() function can be used to retrieve different column values.

Syntax

= LOOKUP(dataSourceName, idValue)

= LOOKUP(dataSourceName, idValue, formatString)

= LOOKUP(dataSourceName, rowNumber, idValue, idColumn, valueColumn)

Parameters

dataSourceName

A string which represents the name of the data source.

idValue

The ID value, or key, used to select the data source record.  If idColumn is not specified, then LOOKUP() uses the first column (column 0) to match idValue.

formatString

The format string applied to the retrieved value.

rowNumber

Specifies the row to be retrieved.  Use ‘null’ if you do not want to specify a specific row number.

If you specify ‘null’ for rowNumber, LOOKUP() retrieves a record from the Data Source containing the ‘idValue’ value in the column specified by the ‘idColumn’ parameter.

idColumn

Number or string specifying the column containing the ID or key value used to select the appropriate row.  If idColumn is a number, LOOKUP() uses that column number, which is 0 based (0 is the first column, 1 is the second column, etc.).  Otherwise, idColumn is treated as a column name string.

If you edit your Data Source, columns may be rearranged.  So if you plan to edit Data Sources, it’s best to use column names rather than column numbers.

valueColumn

String specifying the column name containing the data element to be retrieved.  LOOKUP() returns the value contained in the ‘valueColumn’ column.

Return Type

String[]

Examples

Example

Returns

=LOOKUP(UnitPriceMinQuery,null,EmployeerID,”EmployeeID”,”UnitPriceMin”)

Searches the UnitPriceMinQuery data source for the record containing EmployeeID in the column named ‘EmployeeID’.

Returns the value located in the field named ‘UnitPriceMin”.

=LOOKUP(UnitPriceMinQuery,null,EmployeeID,0,1)

Searches the UnitPriceMinQuery data source for the record containing EmployeeID in column 0 (the first column).

Returns the value in column 1 (the second column).

Since LOOKUP() functions simply return values, they can be combined in more complex formulas.  For example, this combination calculates a percentage by dividing two the values returned by two LOOKUP() functions:

= Format(ParseDecimal(LOOKUP(UnitPriceSumQuery1, EmployeeID)) / ParseDecimal(LOOKUP(UnitPriceSumQuery,"")), "p")

PARSEDECIMAL and FORMAT are needed because LOOKUP returns either a string or an object.  PARSEDECIMAL converts the values returned by LOOKUP into numbers, and FORMAT converts the percentage into a displayable string.