Home > Blog > USING VLOOKUP VALIDATION RULES IN SALESFORCE

USING VLOOKUP VALIDATION RULES IN SALESFORCE

Today we will talk about the purpose that Salesforce VLOOKUP function serves. 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 Salesforce VLOOKUP formula 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. Syntax :- VLOOKUP(field_to_returnfield_on_lookup_objectlookup_value) Field_to_return:- the field that contains the value you want to be returned. Field_on_lookup_object:- the field on the related object that contains the value you want to match. Lookup_value:- 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.
  1. Create one custom object ZIPCODE
  1. Upload test data
  2. Create a Validation Rule
FORMULA:-                 Vlookup( $ObjectType.ZipCode__c.Fields.State__c ,                                      $ObjectType.ZipCode__c.Fields.Name ,                                       BillingPostalCode ) != BillingState        
Enquire Now
X

lets get over a cup of coffee and discuss!