+ Start a Discussion
Bhushan Mahajan 5Bhushan Mahajan 5 

What is v-lookup function in salesforce?.

What is v-lookup function in salesforce?.
 I want to know about v-lookup function

why this function used in salesforce.and  how to use this  V-lookup function in slesforce.?.
 
Chamil MadusankaChamil Madusanka
It searches an object for a record where specified field matches the specified lookup value. If a match is found, returns another specified value.Syntax of vlookup:
VLOOKUP(field_to_return, field_on_lookup_object, lookup_value)

Here I will give a simple example, how to use VLOOKUP function in validation rule.

Example: Create validation rule that checks the “Billing ZIP/postal code” entered against a table to validate that the zip code and state match.

To track ZIP Codes I have created custom object called “Zip Code” in this object I am storing zip code, states & countries. And created below records (sample records) in this zip codes object.

ZIP     Code State    City
35004 Alabama       Moody
35005 Alabama       Adamsville
35010 Alabama       Alexander City
90001 California      Los Angeles

Below validation rule validate Billing zip/postal code in account object with the codes in zip code object. If you enter wrong zip code this validation rule throw an error.

Creating validation rule:

To create validation rule go to setup -> Build -> Customize -> Account -> Validation rule and enter required information.

Error condition formula:

VLOOKUP($ObjectType.Zip_code__c.Fields.State__c, $ObjectType.Zip_code__c.Fields.Name , LEFT( BillingPostalCode ,5))<> BillingState

To test this validation rule go to Account tab and create new account record with enter billing city = Los Angeles, Billing State/Province = Califirnia and postal code = 3099 and save this record, you will get associated error message. If you enter correct data which is mentioned in above table, record will save. If you enter wrong data validation rule will fire an error.
Bhushan Mahajan 5Bhushan Mahajan 5
 I have use this formulaon validations but they are not working. pleae tell me right formula.

" VLOOKUP($ObjectType.Zip_code__c.Fields.State__c, $ObjectType.Zip_code__c.Fields.Name , LEFT( BillingPostalCode ,5))<> BillingState".

tell me another formula.
 
Chamil MadusankaChamil Madusanka
Refer this link Bhushan. 
https://success.salesforce.com/answers?id=90630000000gyFzAAI (https://success.salesforce.com/answers?id=90630000000gyFzAAI" target="_blank)

And here are some example of vlookup: http://focusonforce.com/configuration/salesforce-vlookup-example/
Warren Walters 18Warren Walters 18
You can create a flow in SF that will return values based on what is searched but the standard vlookup function only works on a validation rules.
Here is a video explanation of how VLOOKUP function works. 
https://youtu.be/s6qxZ96MZKQ

Some things to remember about the VLOOKUP function
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_object must be the Record Name field on a custom object.
The field_on_lookup_object and lookup_value must be the same data type.
If more than one record matches, the value from the first record is returned.
The value returned must be on a custom object.
You cannot delete the custom field or custom object referenced in this function.
This function is only available in validation rules.