Salesforce VLOOKUP Function

In this post, we will learn about What is Salesforce VLOOKUP Function in Salesforce? When we should use Vlookup function and how to create VLOOKUP?

What is VLOOKUP function in Salesforce?

VLOOKUP function returns a value by looking up a related value on a custom object similar to the VLOOKUP() Excel function. It function is only available in validation rules.

VLOOKUP function Syntax

VLOOKUP(field_to_return, field_on_lookup_object, lookup_value)

field_to_return :- the field that contains the value you want returned
field_on_lookup_object :- the field on the related object that contains the value you want to match
lookup_value :- with the value you want to match

Salesforce VLOOKUP Function Example in Salesforce

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.

Let’s see how to create the VLookup in Salesforce.

Step 1) Create one custom object ZIPCODE

Create one custom object called ZipCode in Salesforce to store the data.

Step 2) Upload some Test data.

Once the object is ready. Upload the all valid zip code in your custom object like below screen shot

Step 3) Create Validation Rule

Not it time to create a Validation rule using the Vlookup function in Salesforce. Now got an account object where you want to create a validation rule. Then use Vlookup like below.

Vlookup Validation Rules

Vlookup(
       $ObjectType.ZipCode__c.Fields.State__c ,
       $ObjectType.ZipCode__c.Fields.Name ,
       BillingPostalCode
) != BillingState

Step 4) Testing

Once you are ready go to the object and try to create an account record with a postcode which is not available on our custom object. Then I will throw a validation error “Please enter a valid Zip Code”

Summary

VLOOKUP is one of the most ignored functions in Salesforce But this is very useful for data consistency. Please share your use case where you used this function in your project.