Explore Functions for Range and Lookup Tables

Learning Objectives

After completing this unit, you’ll be able to:

• List the two functions used for range and lookup tables.
  
• Explain the syntax of vlookup() and range_lookup() functions.
  

Functions Using Range and Lookup Tables

There are a couple of functions in Salesforce Spiff that use either a range or a lookup table. The vlookup() function is for lookup tables, while the range_lookup() function is for range tables. Let’s find out more about each.

vlookup()

The vlookup() function finds a matching lookup value from a lookup table and returns the specified column value on that row.

It’s syntax is vlookup(lookup_value, table_name, return_value_column_name (optional), exact_match(optional), default_value (optional)).

Here’s what the arguments mean.

lookup_value: Values that can be referenced are located in the first column of your lookup table as lookup_value. If you're using a name or any other type of string data, wrap your lookup value in quotations. Numbers don’t require quotation marks. Formulas can also be used as lookup values.

table: This is the name of the lookup table created in Spiff. When Spiff recognizes the lookup table, its name turns purple in the formula bar.

[return_column]: If your table only has one return column, this argument can be left blank. If you have more than one return column, the return column name must be specified in quotation marks.

[exact_match]: The default value for exact_match is set to true. Set exact_match to false if there could be variation in the capitalization or spaces in the lookup_value. For example, if my lookup value is "Sales Manager," but I want "sales manager" to provide me with the same result, I can choose false for this argument.

[default_value]: If a default_value is provided, it is returned when a row is not found matching the lookup_value.

range_lookup()

The range_lookup() function is similar to vlookup() but used for a range of values from a range table. Consider this sample renewal table. If the rep’s ContractExpansionPercentage is 87%, it falls in Tier 3, and the function returns 1.5% from the Renewal Rate column of the range table.

The range_lookup() function syntax is range_lookup(lookup_value, table_name, tie_break_below(optional), return_value_column_name (optional))

Let’s go over the arguments.

lookup_value: This argument represents the value that’s compared against the range table.

table: This argument references the range table created to determine the appropriate commission rate.

[tie_break_below]: This optional argument determines what happens if the value entered is present in multiple tiers. If no argument is entered for the tiebreak, the default is the tier with the lower value. So if the value argument is exactly 85%, the default tiebreak value returns 1% (Tier 2). Entering true as the tiebreak parameter uses this default. Entering false causes the formula to return the higher tier, 1.5% (Tier 3).

[return_column]: This optional argument defines a specified return column. Use it if multiple return columns exist on a range table. If only one return column exists on the range table, it’s selected by default without this argument. A specified return column must be wrapped in quotation marks.

In this module, you learned how to set up range and lookup tables and use their respective functions. It’s time to incorporate them into your commission plans for your organization!