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
chanti kchanti k 

why we can't use V lookup functionality on standard objects

Hi All,

why we can't use V lookup functionality on standard objects ? we can impliment only on Custom object. what is the reason, Any one can expalin this.
NagendraNagendra (Salesforce Developers) 
Hi Chanti,

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:
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:


Hope this helps you.

Best Regards,
Kshirabdhi SahuKshirabdhi Sahu
Hi Nagendra,
You have explained how to use VLOOKUP on custom objects. But the question was why this function can be used only on custom objects but not on standard objects.
Simon Kinder 19Simon Kinder 19
OMG that wall of spam from Nagendra... signed up on this site just to downvote someone who does not read questions at all and launches a spam answer...

On the subject... makes little sense why this only works for custom objects
Walter White 2Walter White 2
VLOOKUP functionality is not available for standard objects in Salesforce because it can bypass data security and sharing rules, standard objects have complex data structures, it requires ongoing maintenance with Salesforce updates, and it can lead to performance issues. Salesforce offers other features like formula fields, roll-up summaries, and relationships to achieve similar functionality while maintaining data security and integrity.
Following links might be help you: