USING VLOOKUP VALIDATION RULES IN SALESFORCE

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.

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

 

 

 

 

Please follow and like us:

Leave a Reply

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