Explanation of the Lookup Function

To derive the value for Service End, a Lookup function is used. Although the calculation of the Service Start Date plus a Term of n months is a simple formula in Excel, we are demonstrating how to use the Lookup function to accomplish the same goal.

Lookup uses Key fields, Service Start and Term in this case. It looks for the value of the Key fields in another ESM model, which is Opportunity - Daily Calendar in this case. When Service Start and Term are found in the same transaction in Opportunity - Daily Calendar, the value of Service End is returned to ESM model Opportunity Data and fills the Service End field (the field with the Lookup function).

Here is the definition of Opportunity - Daily Calendar and its data.

The rows of data continue all the way down to 12/31/2021 with a term of 36 months ending 12/31/2024.

For example, the first row in our opportunity data has a Service Start of 02/14/2019 and a Term of 24. Looking up these Key fields in the Opportunity - Daily Calendar returns a Service End date of 02/14/2021.

For more information about the Lookup function and other functions, see External Source Model Field Types, Expressions, and Formulas.