Salesforce VLOOKUP is a lesser-known function but one that can be very useful when you need to enforce data quality or you need to allow a user without admin rights to make impactful changes.
The Salesforce VLOOKUP function is very similar to that in Excel (check out the section on VLOOKUP in this blog post). A VLOOKUP is used to take data I have now and use it to look up something else and compare.
The most common example in Salesforce is Zip Codes, but you can also use it to validate against a list of anything (States, Countries, Job Titles, Area Codes, etc.). In this article, we’ll demonstrate how this works by validating phone area codes.
Create a Salesforce VLOOKUP
Use Case
A company only sells in the United States. They have a rule saying that any Contact record that is marked as “Primary” must have a US Phone Number. We already have a Regex Validation Rule that enforces the formatting of the Phone Number:
!REGEX(Phone,”^((d{3})s?d{3}-d{4})?$”)
The Regex Validation Rule states that the phone number must be in the format (xxx) xxx-xxx but does not give us any way to validate that an Area Code is a valid US Area Code. So, in theory a user could enter (999) 999-9999 which is no use to us.
There are too many Area Codes to fit in the limit of a Validation Rule.
We also want our Sales Operations Manager to be able to manage and amend the list of valid Area Codes but we do not want to make her the System Administrator.
That’s quite a few requirements and information to take in so let’s break it down:
- Company only sells to the United States
- Company has a checkbox on the Contact called “Primary”
- When this box is checked the Contact Phone Number must have a valid US Area Code
- We know what all the valid US Area Codes are but the list is very large and it can’t fit in a Validation Rule
- We are already enforcing Phone Number format with a separate REGEX Validation Rule
- !REGEX(Phone,”^((d{3})s?d{3}-d{4})?$”) will give you (xxx) xxx-xxx
- We want the Sales Operations Manager (non-administrator) to be able to add new Area Codes as needed
All of these requirements can be managed with Salesforce VLOOKUP. This will allow us to take the Area Code of a Contact, compare it to the list of Area Codes, and validate if it is acceptable or not.
Step 1 – Create a Custom Object and Import Records
The first thing we need to do is create a custom object to hold the acceptable values. We’ll call this Custom Object “Area Codes”. Because this is a Custom Object, we can give our Sales Operations Profile Users create permission on this Object.
As we are allowing a non-Admin user to create or edit these records, I have opted to enable Field History and created a Custom Tab. Our Record Name should be set to Text (not auto-number). We don’t need any other fields on this Object for this example. Our Record Name is going to be the valid Area Code. Go ahead and set your Field History and Permissions now.
Once that is done you can mass create all the valid Area Code Records (use Data Loader or the Data Import Wizard!).
Step 2 – Create a VLOOKUP Validation Rule
Now that our list of valid Area Codes are loaded into Salesforce and Sales Operations can add more as needed, we’re ready to begin building the VLOOKUP Validation Rules. The VLOOKUP Formula will be built in two parts, because it needs to do two things: find the matching value and then compare it to the current value on the record. In this example, we want to find the valid Area Code and compare it to the Area Code on the Contact Record.
The first half of the Validation Rule starts like this:
This is how we are going to grab the valid area code from our Area Codes Object.
Field_to_return: the field from the Custom Object that is holding the value I want to look for. In this example, I want the record Name from the Area Code Object.
Field_on_lookup_object: the Record Name Field of the Custom Object (in this case it happens to be the same as the value we just pulled above). This will always be the Record Name of the Custom Object.
Lookup_value: the value on this Record that I want to use to find the right Custom Object. In this example, it’s the Phone but we only need to use the left 5 characters.
Now, let’s start populating this.
The field_to_return is found by going to “Insert Field” -> $ObjectType ->Area Code-> Area Code Name (the custom object, the field we want to return)
Note: If you are validating something else, like Zip Codes, you may have multiple fields on your Custom Object. Grab the field you need.
The field_on_lookup_object is asking for the Name field of the Custom Object. You can follow the exact steps above, or just copy and paste.
The lookup_value is the field on this object I want to use to find a match. In this case, the Phone Number, but only the left 5 characters: LEFT(Phone,5)
Completed first half of our Validation Rule:
VLOOKUP( $ObjectType.Area_Code__c.Fields.Name , $ObjectType.Area_Code__c.Fields.Name, LEFT(Phone,5) )
Remember this is only the first half of our Validation Rule! If you click “Check Syntax” at this point you’ll get an error message.
Now our VLOOKUP is pulling the valid Area Code from among our Area Code Records but we still need to take the value that we have received from this VLOOKUP, and compare it to the actual value of the field.
This is the easy part! We just need to add the condition that will cause an error:
<> LEFT(Phone,5)
What this says in plain English: Compare the Area Code on this Contact with the Area Code list. If you can’t find a match, show an error message.
We had one more condition from our use case: This rule only applies when the Contact “Primary Contact” checkbox is true. We can add that as an additional statement:
Step 3 – Testing
Time for testing! Our Scenarios are:
- Primary Contact with Bad Area Code – expect to see error message
- Primary Contact with Good Area Code – expect to save
- Primary Contact with No Phone Number – expect to save
- Non-Primary Contact with Bad Area Code – expect to save
- Non-Primary Contact with Good Area Code – expect to save
- Non-Primary Contact with No Phone Number – expect to save
It works!
Summary
There are a number of considerations to keep in mind when using VLOOKUP Validation Rules but the two biggest are:
- This feature only works as a Validation Rule not as a formula – wouldn’t that be cool?! (Vote on this idea up on the Idea Exchange).
- This feature only works for Custom Objects.
I hope this helps shed some light on a lesser-known feature. It’s a great option for a declarative admin to compare data against a large data set. Let us know in the comments your favourite use cases for VLOOKUP Validation Rules!
Comments: