Description
The LOOKUP function allows you to search for a specific value in another table, even an unrelated table.
This is useful for scenarios where a value that would be useful for your analysis is stored in a different table. Essentially, you can “lookup” values from other forms that are not related by a reference field. For example, you might need to find the specific exchange rate that is applicable on a certain date or determine the correct weight to apply to a survey response based on specific variables like gender and province.
Usage
LOOKUP ( [search key], [lookup form], [lookup form key], [lookup value] )
[search key]: The value in the current table (or form) that will be used as the reference for searching values in the lookup form.[lookup form]: The form ID or table name where the values will be looked up.[lookup form key]: The column or expression in the lookup form that will be matched with the[search key].[lookup value]: The column or expression in the lookup form that will be returned when a match is found.
Remarks
The key value you are using to search the lookup table, must be unique in the lookup table. If there is more than one row with the same value, the result of LOOKUP() will be blank.
Examples
Exchange rate lookup
Suppose you have a table with records containing the amounts for loans issued and another table that lists
exchange rates by date as shown below:
Loans form:
| Loan ID | Local currency value | Date of issue |
|---|---|---|
| LOAN-2023-001 | 1000 | 2021-01-01 |
| LOAN-2023-002 | 1500 | 2021-02-15 |
| LOAN-2023-003 | 2000 | 2021-03-20 |
Exchange rates form:
| Date | Rate |
|---|---|
| 2021-01-01 | 0.5 |
| 2021-02-15 | 0.55 |
| 2021-03-20 | 0.6 |
The loans are denominated in the local currency but you need to calculate the total loan value in USD. To achieve this, you can add a calculated measure using LOOKUP to find the exchange rate on the date of issue. You can then combine this with SUMX to calculate the total value of all loans in USD as follows:
SUMX([Loans form], [Local currency rate] *
LOOKUP([Date of issue], [Exchange rate form], [Date], [Rate]))
This gives us a final result of 2525 USD.
This expression multiplies the local currency value of each loan by the corresponding exchange rate found in the [Exchange Rates] form based on the loan's [Date of Issue]. The sum of the converted loan values is then calculated.