Many people are aware of the Microsoft Excel vlookup function, that will return a value in a table (or defined range of cells) based on an identifier. Salesforce has a function with the same name, and this post will explain how it can be used using an example.

Let's say that you have a business requirement that only cases that have been open for a certain period of time can be set to High priority. For example, you only want cases that have been open for at least 12 hours to be set to High priority, otherwise an error should be displayed.

We can do this with a validation rule, but what if you would like the number of hours to be set by someone that is not the Salesforce Administrator and is not familiar with validation rules? Alternatively you may occassionaly have the need to update this value as an Administrator, but do not want to change the rule and update all other environments to be in sync.

By storing the value of the number of hours in a parameter object, it can be maintained by any appropriate user. In the example below, we can see a record created in the 'Parameter' object. This is a simple object that just contains a code and value. In our example we have created a parameter with the code 'CASE_OPEN_HOURS' to represent the number of hours that a case needs to be open before it can be set to High priority. This record has the value '12' to represent 12 hours. 

Now that we have our Parameter defined, we can use it and the vlookup function in a validation rule.

This validation rule is checking if the number of hours the case has been open is smaller than the parameter values and the Priority picklist value is set to 'High' then display the error 'Case has not exceeded open hours'.

Lets have a closer look at how the Salesforce vlookup function has been used:

The syntax is : VLOOKUP(field_to_return_from_lookup_object, field_to_compare_on_lookup_object, value_to_compare)

In our example:

The field to return from the lookup object is the 'Value' field.

The field to compare on the lookup object is the 'Name' field (we have renamed to have the label 'Code')

The value to compare is our Parameter Code, in this case: 'CASE_OPEN_HOURS'.

VLOOKUP($ObjectType.Parameters__c.Fields.Value__c, $ObjectType.Parameters__c.Fields.Name, "CASE_OPEN_HOURS")

Now with the validation rule in place, if we try to update a case to 'High' priority when it hasn't exceeded the open hours (set at 12 in the Parameter object), an error will be displayed:

However, if the case has exceeded the parameter value, we can update the priority.

So there we have an example of using the vlookup function in a validation rule, that has saved us from 'hard coding' the value. Any users that we give access to the parameter table can update the value.

Some Points to note regarding the vlookup function:

• vlookup only works on custom objects
• Can only be used in validation rules
• Can be used to prevent duplicate records
• The field to lookup must be the Name field