Export (0) Print
Expand All

Lookup Function


Lookup(source_expression, destination_expression, result_expression, dataset)








A constant that specifies the name of a dataset in the report. For example, "Products".

Returns a Variant, or Nothing if there is no match.

Use Lookup to retrieve the value from the specified dataset for a name/value pair where there is a 1-to-1 relationship. For example, for an ID field in a table, you can use Lookup to retrieve the corresponding Name field from a dataset that is not bound to the data region.

Lookup does the following:

  • Evaluates the source expression in the current scope.


  • On the first match of source expression and destination expression, evaluates the result expression for that row in the dataset.

  • Returns the result expression value.

To retrieve multiple values for a single name or key field where there is a 1-to-many relationship, use LookupSet Function. To call Lookup for a set of values, use Multilookup Function.

The following restrictions apply:

  • Lookup is evaluated after all filter expressions are applied




  • Lookup cannot be used as an expression for the following report items:

    • Dynamic connection strings for a data source.

    • Calculated fields in a dataset.

    • Query parameters in a dataset.

    • Filters in a dataset.

    • Report parameters.

    • The Report.Language property.

In the following example, assume that a table is bound to a dataset that includes a field for the product identifier ProductID. A separate dataset called "Product" contains the corresponding product identifier ID and the product name Name.

In the following expression, Lookup compares the value of ProductID to ID in each row of the dataset called "Product" and, when a match is found, returns the value of the Name field for that row.

=Lookup(Fields!ProductID.Value, Fields!ID.Value, Fields!Name.Value, "Product")

Community Additions

© 2014 Microsoft