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!
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 Owner
- State__c (picklist)
- 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.”)
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!
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.
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:
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!
14 thoughts on “ Using VLOOKUP Validation Rules in Salesforce ”
Hi thank you for this !
Maybe I wonder wrong …
but I wonder : what if VLOOKUP matches more than one record ?
If it matches more than one record, it will only process the first record.
Great article with a small typo:
In other words, of the License Credential is expired…
In other words, if the License Credential is expired…
If, not of.
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.
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!
Ah, got it! If I ever want to trip somebody up on an interview, I’ll ask about VLOOKUP. 😉
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 !!
You may be able to do this but may require some updates to your validation rule. Not entirely sure what that would be.
Thanks for posting this. I’m having an issue with my use case in the Non-Profit Success Pack scenario. As you probably know, in NPSP the standard Account-Contact relationship represents the Household-Individual relationship, and the Affiliation custom object links the Individual to his/her Employer.
We are using the Case object for internal project tracking and want to make sure the Account on the case is the contact’s employer. Since the Name field on Affiliation object is an Auto-Number, I created another custom object to hold those affiliations for my validation. When a record is created in the Affiliation object, it will be mirrored into my Affiliation_Validation__c object, with the name being the Account ID concatenated with the Contact ID and a checkbox field to be used for the field_to_return.
My validation rule looks like:
The tricky part is that if John Smith is NOT affiliated with ACME Corp., there is no record at all in the Affiliation_Validation__c object.
Can I get the Validation Rule to pop an error if there is no record?
I would like to create a validation rule to lookup assets and accounts to prevent duplicates. In excel, i would simply do a vlookup. According to the above, it sounds like a vlookup would not be possible in this situation since asset and account are not custom. Is that correct? Is there a different way to match asset and account and run a validation against it?
Hi Ann! Yes, that’s correct (assuming you’re using the standard Assets table). The only other way I could think to make this work in your use case would be a trigger which would evaluate the two tables with your given criteria and return an error when appropriate. Depending on the process flow and what you’re trying to accomplish, a Flow may work well to guide users through the process of entering data in a more structured way.
I was very confussed, but you brought a very clear and detailed explanation
Thanks a lot!
When we use Apex trigger to perform a validation by using SOQL, it will make a single SOQL query to validate against 200 records.
If we use VLookup to perform the same validation, will it make one SOQL query for each record in the background?
If it is true, it means that it has to make 200 SOQL queries to validate against the 200 records. In that case, the VLookup approach is inferior than the Apex trigger approach in terms of efficiency.
Would like to seek your comments. Thanks.