DLOOKUP

Syntax

DLOOKUP (Dimension, Data, Condition, {Direction})

Description

The DLOOKUP function returns Data for the first Member in Dimension where Condition is true. If Direction is omitted or zero, the function scans forward from the first member. If Direction is nonzero, the function scans backward from the last member. If no member in Dimension fulfills the Condition, the function returns zero.

Returns

Data for the first member in Dimension where Condition is true. If Direction is omitted or zero, the function scans forward from the first member. If Direction is nonzero, the function scans backward from the last member. If no member in Dimension fulfills the Condition, the function returns zero.

Example

Suppose that a company awards its salespeople a 10 percent commission if their sales are at least USD 50000, an 8 percent commission if their sales are at least USD 30000, a 5 percent commission if their sales are at least USD 15000, and a 1 percent commission if their sales are less than USD 15000. One way to calculate the commission is to create a lookup table. Define a dimension called RANGES and attach it to data cubes called SALES_MINIMUM and LOOKUP_RATE. Each number in SALES_MINIMUM defines the minimum value for the sales range, while the next number defines the upper limit for the range. LOOKUP_RATE holds the commission rate for each range. Use the following formula to calculate the commission rate:

DLOOKUP(RANGES, LOOLUP_RATE, SALES >= SALES_MINIMUM, 1)

Because the last argument of DLOOKUP is 1, the function starts with the last member of RANGES and scans backwards until SALES is greater than or equal to SALES_MINIMUM. It is important to scan backwards to find the highest lookup rate for which the condition is true. Otherwise, the formula returns the lowest lookup rate no matter how high the value of SALES is.