Blog

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.

salesforce vlookup example

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")

salesforce vlookup example

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

What Certification are you studying for now?

Focus on Force currently provides practice exams and study guides for ten certifications

Salesforce VLOOKUP Example

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… Read More

Salesforce VLOOKUP Example

Salesforce Multiple Record Types Example

Creating Multiple Record Types Creating brand new custom objects in Salesforce, while necessary, can be time consuming and tedious. Often times you will need to create a different page layout or new selections for a particular picklist field but the only way you know how to do this is create an entirely different custom object and customize it as needed. This is where creating multiple record types for one object… Read More

Salesforce Multiple Record Types Example

Creating a Dynamic Dashboard in Salesforce

Dashboards are a great way to represent data visually within an organization. They can show how you have performed in the past and what’s happening at the moment – two keys to any business that wants to be successful.Normally, dashboards in Salesforce are run based on one user for the entire organization. Meaning the dashboard looks the same for all users within the organization. This is great if you are… Read More

Creating a Dynamic Dashboard in Salesforce

Salesforce Web to Lead

The Salesforce Web to Lead functionality offers an easy way to get leads from your website into Salesforce. Although there are some constraints with its use, it will satisfy the requirements for many organizations. Salesforce also offers a way to associate the leads from the webpage to a particular campaign. You can also have multiple web-to-lead forms from multiple websites or landing pages, all inserting leads in Salesforce and either… Read More

Salesforce Web to Lead

Reporting on Accounts that haven’t ordered a product in the last 12 months

It is quite common for organizations to bring order history into Salesforce to add to other interactions captured in Salesforce to have a complete picture of a customer. Once the information has been brought into Salesforce, it is easy to report on account order history, but what if you would like to report on accounts that don’t have order history for a particular product in the last 12 months. This… Read More

Reporting on Accounts that haven’t ordered a product in the last 12 months

Reporting by Month and Year with Salesforce Reports

You may have the requirement to report by month and year. If you have tried that, you will find that the standard reporting features may not provide you with what you are looking for. Lets say that you would like to report on the number of closed opportunities over the last 3 years. You would like to have months reported along the top in columns and the years down the… Read More

Reporting by Month and Year with Salesforce Reports

What can you do with SalesforceA

SalesforceA was released in Spring 14 to allow Salesforce admins the flexibility to do routine user management on their mobile device. If you have ever had to immediately inactivate a user, or had to response ASAP to a user that has locked themselves out and needs a password reset, then this app will come in handy. Here is our review of what you can do with this app, what’s good… Read More

What can you do with SalesforceA

Salesforce Sandboxes

What is a Salesforce Sandbox? Although it is very easy and possible to make changes directly to configuration in Salesforce, it is a better idea to test out those changes in another environment which won’t affect your users. Thats why Salesforce includes a ‘sandbox’ for every production organisation where changes can be made and tested before moving them to production. This type of environment is called a sandbox. The number… Read More

Salesforce Sandboxes

Salesforce Quotes

Once you have added your products into an opportunity, Salesforce has inbuilt functionality to allow you to generate and email quotes to customers. Lets take a look at how this works, and what you need to be aware of when using Salesforce Quotes. First, to use Quotes they need to be enabled. By default the are not enabled and you won’t see them in your Opportunity screen. Go to: Setup… Read More

Salesforce Quotes

Salesforce Jobs

Salesforce Administrator After Salesforce is implemented, a Salesforce administrator is required to maintain and extend the system functionality as business needs change, evolve or expand into new areas of the business. The role is a varied one but will typically include some or all of the following: • Provide User Support and be responsible for user administration • Investigate and resolve problems • Investigate and analyze requests for change and… Read More

Salesforce Jobs

Salesforce Basic Terminology

If you are new to the Salesforce.com application, you may wonder ‘What exactly is Salesforce.com’ and before long you will come across some terminology that is used to describe different parts of the application, functionality and navigation.Here is a quick guide to some of the basic terminology you need to understand Salesforce.com and navigate the application.Basic SalesforceSalesforce is the name of the company and name of the application. It started… Read More

Salesforce Basic Terminology

Subtract Two Date Fields to Calculate Duration of Time

If you are faced with the challenge of calculating the duration of time between two dates in Salesforce, this post is for you. Lets say what you want is to take two dates and times, for example 20/01/2014 09:00am and 21/01/2014 10:20am and calculate the duration of time between them in hours and minutes, and display the result like this: 24:20 or 24h:20m.If we just create a formula field and… Read More

Subtract Two Date Fields to Calculate Duration of Time

Salesforce Roll-up Summary Using the Value of a Formula Field

Roll-up summary fields are a very useful Salesforce feature, but you may come across a situation where you need to rollup the value of a formula field. Here is an example: You have added a custom field to the Product object to record the cost of a product. You want the total cost of all products added displayed on an opportunity. In the screenshot below you can see Widget A… Read More

Salesforce Roll-up Summary Using the Value of a Formula Field

Saving Emails to Salesforce

If you use Gmail or another web based email service, Salesforce has a feature that will allow you to save your emails related to leads, contacts and open opportunities. It is the ‘Email to Salesforce’ features and when enabled, allows users to bcc a unique bcc email address to automatically create activity records against records in Salesforce. To use this feature, firstly the Salesforce Administrator has to enabled it, and… Read More

Saving Emails to Salesforce