LOOKUP function
As you’ll see in the pages on arrays, you can tell Openbox to repeat calculations by things like scenarios, locations or business units.
Sometimes, you will want to pick out a single one of these repeated calculations. LOOKUP (and its sister function SELECT) let you do this. The syntax is:
LOOKUP(<line item>, <value to lookup>, <array to look up>)
The <array to lookup> is only needed if the line item varies by more than one array.
EXAMPLE
Suppose you have a model with ‘Sales’ as an input, and this varies by location – so you might have sales in Europe, sales in USA and so on.
Suppose also that you have ‘Revenue’ calculated as ‘Sales’ * ‘Price’. Since ‘sales’ varies by location, ‘Revenue’ will as well. If you just want to look at revenue in Europe, you can use the LOOKUP function like this ‘LOOKUP([Revenue],’Europe’).
There is an additional functionality of Lookup worth mentioning. Rather than having hard coded, you can flex it. If you want to define in InpC (rather than in the formula) what array-element to use you can define a constant and call it for instance [Region selected]. You can then use LOOKUP([Revenue],[Region selected],”Region”). I applied the principle also for switching the currency in which an Openbox report is denominated. Rather than making a separate report in another currency you can use a single report and just change the ‘value’ in InpC for [Currency selected] from USD to EUR.