Today we will talk about the purpose that VLOOKUP function serves in Salesforce.
It searches an object for a record where specified field matches the specified lookup value. If a match is found, it returns specified value. The VLOOKUP formula in Salesforce works a bit differently than Excel.
There are few constraints on the formula that can never be overlooked.
- 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.
Salesforce defines the formula as: “It 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 in a validation rule, so nothing is “returned” as in the Excel function.
:- VLOOKUP(field_to_return, field_on_lookup_object, lookup_value
:- the field that contains the value you want to be returned.
:- the field on the related object that contains the value you want to match.
:- the value you want to match.
VLOOKUP Example in Salesforce Step by Step process:
In this example, we will check that a billing postal code is valid by looking up the value in a custom object called ZipCode__c that contains a record for every valid zip code. If the zip code is not found in the ZipCode__c object or the billing state does not match the corresponding State__c in the ZipCode__c object, an error will display.
- Create one custom object ZIPCODE
Vlookup( $ObjectType.ZipCode__c.Fields.State__c ,
BillingPostalCode ) != BillingState
- Upload test data
- Create a Validation Rule