VLOOKUP formulas are very beneficial in Excel, but did you can use them in Salesforce too? Unfortunately, the documentation wasn’t clear on how to use this feature, and I had a hard time understanding the syntax. Three pages of Google search results didn’t help one bit.

After posting to the success community, I got some help and all became clear! Now that I understand how all of this works, I’m going to share with you how to use the VLOOKUP validation rule!

Formula Details

The VLOOKUP formula in Salesforce works a bit differently than Excel. If this causes confusion, let me just tell you now; forget the Excel formula altogether and start with a clean slate. That will help to reduce confusion in the syntax.

There are some limitations and general comments on the formula that I want to make mention of at the beginning.

Salesforce defines the formula as “Returns a value by looking up a related value on a custom object similar to the VLOOKUP() Excel function.” While this is true, this formula only works as a validation rule, so nothing is “returned” as in the Excel function. There is no writing of data to another record.

Here are a few more things to know:

• The field_to_return must be an auto number, roll-up summary, lookup relationship, master-detail relationship, checkbox, date, date/time, email, number, percent, phone, text, text area, or URL field type.
• The field_on_lookup can only be the Record Name. Meaning that the only field to match on in the custom object is the record name. Matching cannot take place on any other fields.
• VLOOKUP only works on custom objects. Forget doing a VLOOKUP where you’re looking up to a standard object; can’t do it.
• If more than one record matches, the value from the first record is returned.
• You cannot delete the custom field or custom object referenced in this function.
• The function is only available in validation rules.

These are some significant restrictions for a lot of use cases so be sure to review them for your use case.

The Use Case

A client is in a regulated industry that requires the salesperson to have an active license in the state of the customer. We built out a table called “License Credentials” which holds all of the sales reps, and their licensure by state, and whether the license is active or inactive.

The request was to ensure that sales reps are not assigned Opportunities if that user does not have an active license for the specified state. Here is a quick outline of these particular attributes I’m working within Salesforce:

Opportunity Object

• Opportunity Owner
• State__c (picklist)

License Credentials

• State__c (picklist)
• Sales_Rep__c (lookup to user)
• Status__c (picklist field with values of “Active” and “Inactive”)
• IsExpired__c (checkbox field that users don’t see. It’s updated to “True” via a workflow rule when Status__c field is “Inactive.”)

The Syntax

Before I get into the specifics of the use case, let’s look at the VLOOKUP formula syntax and make sure that we fully understand the various elements.

VLOOKUP( field_to_return, field_on_lookup_object, lookup_value )

field_to_return – in Excel, the field to return is the field that you want to add to a data set. Typically, I use this element of the function to return the Salesforce ID of a record to do data updates. But, remember, that this VLOOKUP function doesn’t actually “return” anything. Instead, this is the field that you want to evaluate as TRUE for the validation rule.

field_on_lookup_object – this is the field on the custom object that we’ll be evaluating for a match on the custom object. It would be good to be able to select any field, but the VLOOKUP function only works with the Object Name field, so this will always be custom object’s name.

lookup_value – the field on the “source” object that you’re attempting to match on the custom object through the validation rule.

If those definitions don’t make too much sense, I’ll show you how it works with the use case next!

The Solution

When I first started writing the formula, I was attempting to match the State__c field from the Opportunity to the State__c field of the License Credentials object. The problem was that many Sales Reps could hold a license in the same state, and I couldn’t differentiate which License Record belonged to which Sales Rep through the formula.

To create a key that was unique, I had to create a custom formula field on both objects. The formula was the same syntax on both objects and cannot be seen by non-System Admin users.

Opportunity = OwnerID+State__c

License Credential = Sales_Rep_ID__c+State__c

The output is a unique key and can now match an Opportunity record to a License Credential record.

//embedr.flickr.com/assets/client-code.js

At this point, I tried to update my VLOOKUP formula to use this new field. But, I was still running into a limitation of the formula: VLOOKUPs only work on record names. While I can choose any object on the Opportunity to match to the License Credential record, the only field on the License Credential I can match with is the Name field.

To solve for this, I created a workflow rule on the License Credential that updates the License Credential Name field “whenever the record is created or edited.” Essentially, the License Credential record could not have any other name except for the unique key.

With this solution in place, I was able to create my VLOOKUP formula on the Opportunity object:

VLOOKUP(
$ObjectType.License_Credential__c.Fields.IsExpired__c ,
$ObjectType.License_Credential__c.Fields.Name ,
OwnerCredVLOOKUP__c )

Note the use of the global variable “$ObjectType” here. It is the first time I’ve used this global variable, and it MUST be used in this formula. I think the reason is that the VLOOKUP formula is potentially cross-object, meaning that I can look for and evaluate data from one object against an entirely unrelated object. The global variable is what makes the logic of this validation rule work.

Here’s how I read this validation rule formula in everyday English:

Return the IsExpired field on the License Credential object by searching the License Credential Name field and match it to the OwnerCredVLOOKUP field on the Opportunity. If the IsExpired field evaluates to TRUE, present the validation rule error.

In other words, if the License Credential is expired, let the user know that the owner does not have an active credential for the state listed on the Opportunity! Here’s what the error looks like in Salesforce.

//embedr.flickr.com/assets/client-code.js

Keep in mind that validation rules prevent users from proceeding with a record change until all errors are satisfied. This validation rule may not work for you in your situation. Alternatively, use an image formula field to somehow indicate that something isn’t right.

I’m sure that there are other use cases for VLOOKUPs in Salesforce, but with its limited functionality, I don’t know how practical the formula is. Have you used this in your org? Provide some examples of your use cases by leaving a comment below!