Using VLOOKUP Validation Rules in Salesforce

Using VLOOKUP Validation Rules in SalesforceVLOOKUP 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.

VLOOKUP Unique String

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.

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!

Load More Related Articles
Load More By Brent Downey
Load More In How To

9 Comments


  1. Eric Bournay

    March 13, 2017 at 10:16 am

    Hi thank you for this !
    Maybe I wonder wrong …
    but I wonder : what if VLOOKUP matches more than one record ?
    Regards
    Eric

    Reply

    • Brent Downey

      March 13, 2017 at 1:03 pm

      If it matches more than one record, it will only process the first record.

      Reply

  2. Darrell

    March 16, 2017 at 5:40 pm

    Great article with a small typo:

    In other words, of the License Credential is expired…

    should read:

    In other words, if the License Credential is expired…

    If, not of.

    Reply

  3. Michael Moore

    May 5, 2017 at 2:21 pm

    I’m a bit confused. At one point you say: “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.”
    But later you say: “If the IsExpired field evaluates to TRUE, present the validation rule error.” That makes sense for a Boolean but how, for example, can a roll-up summary evaluate to TRUE?
    As an aside, I know that you are not advocating the VLOOKUP but simply explaining how it works. I would think putting this validation in a trigger would be a more straight forward approach.

    Reply

    • Brent Downey

      May 5, 2017 at 4:37 pm

      Michael, I totally agree that this would be better logic for a trigger mainly because it’s very limited. You’ll get more options with a trigger. Plus, code can actually perform a true VLOOKUP. That being said, you could evaluate a roll-up summary field in the formula to evaluate to true. For example, “rollup_summary__c > 5” could fire the validation rule. It will really depend on the use case though. Thanks for the comment!

      Reply

      • Michael Moore

        May 5, 2017 at 6:21 pm

        Ah, got it! If I ever want to trip somebody up on an interview, I’ll ask about VLOOKUP. 😉

        Reply

  4. Satya

    October 7, 2017 at 7:15 am

    Hi

    Can we make vlookup search a case insensitive ?
    Meaning while comparing two capitals of a country –> example capital of India is Delhi, while entering records it should allow saving the record for both entries like “Delhi” as well as “delhi”..

    I hope you got my point !!

    Reply

    • Brent Downey

      October 20, 2017 at 1:44 pm

      You may be able to do this but may require some updates to your validation rule. Not entirely sure what that would be.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Sponsors