function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Raju SudheerRaju Sudheer 

Explain VLOOKUP

Shrikant BagalShrikant Bagal
Hello Raju,


Let consider following scenario:
---------------------------------------------------------------------------------------------------------

Suppose that you are creating Employee(a custom object) records in the SF org. Now since Employees are very important records, you do not want the users to create duplicate Employee again and again. For example, say one User already created an Employee Record with name - Arpita Bose. Now, an another User who does not know about this tries to create an another Employee record with the same name - Arpita Bose. and hence there is a potential duplication. This should not be allowed. 

So you have two options now:Add a Validation Rule on the Employee which checks whether there are any existing Employee records with the same name if so stop the User from saving it. Here comes the role of VLOOKUP.
Create an Apex Trigger(before Insert, before Update) which runs a SOQL query to get all the Employee records in the Org with same name as of the one thats getting created now. If the number of records is 1 then stop the User from creating the Employee using an addError method. This will require considerable development effort.Now, since we are interested in VLOOKUP lets see as of how we can do the same. Create a Validation Rule as below:

(4) Name = VLOOKUP(
(1) $ObjectType.Employee__c.Fields.Name ,  
(2) $ObjectType.Employee__c.Fields.Name ,  
(3) Name)
 
Now, our basic requirement is to check if an another Employee with the same Name exists or not. So its obvious we are looking into the Name field. The logic is quite simple compare the Name (4) on the Current Record and theName on that Employee record that matched with the same Name as of the one that we are creating now. Hence we use the equality operator (=). 

Lets look into the VLOOKUP side. You could express the VLOOKUP formula as below: "Look into the Name(2) field on all the Employee Records and return value in it's Name (1) field if the it happens to be same as the Name(3) on the Current Record." 

The SFDC Document define VLOOKUP as follows:

VLOOKUP(field_to_return, field_on_lookup_object, lookup_value)
 
Searches an object for a record where the specified field matches the specified lookup_value. If a match is found, returns another specified field value.
Points to Remember:VLOOKUP only available on Custom Objects. Vote for this idea: https://success.salesforce.com/ideaView?id=08730000000BqPs
VLOOKUP only available in Validation Rules.
VLOOKUP can only be done on the Name fields.
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, picklist, text, text area, or URLfield 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.
----------------------------------------------------------------------------------------------------------------------------------------
Following links might be help you:

- https://success.salesforce.com/answers?id=90630000000gyFzAAI
- http://www.salesforcetutorial.com/using-vlookup-enforces-data-consistency/
- https://www.interactiveties.com/blog/2013/salesforce-vlookup-function.php#.VaYgQfmqqko


Hope this helps!

Thanks!,
Shri
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 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.