Ask Search:
Arpita BoseArpita Bose 

VLOOKUP explanation needed with example

Hi
Can anyone please explain the use of VLOOKUP() function in salesforce with a good example, other than the common ZIP_CODE_c ?
As I am new to salesforce, this function is very confusing for me.

Thanks
Best Answer chosen by Moderator (salesforce.com) 
Deepak AnandDeepak Anand
Hey Arpita,

Forget about the Zip Code example. Lets take an another one for now.

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:
  1. 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.
  2. 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 the Name 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:
  1. VLOOKUP only available on Custom Objects. Vote for this idea: https://success.salesforce.com/ideaView?id=08730000000BqPs
  2. VLOOKUP only available in Validation Rules.
  3. VLOOKUP can only be done on the Name fields.
  4. 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 URL field type.
  5. The field_on_lookup_object must be the Record Name field on a custom object.
  6. The field_on_lookup_object and lookup_value must be the same data type.

Hope this helps!

Best Wishes,
Deepak
 




All Answers

Amit GuptaAmit Gupta
Hi Arpita,

It is very similar to Vlookup funstion in Excel..
Check this links for more detail
http://developer.force.com/cookbook/recipe/validating-data-based-on-fields-in-other-records

Note:- We can use Vlookup only in validation rule.


VLOOKUP() lets you do a join between two data sets that are in Excel. It doesn’t have the power of Access queries, but if all you want is the Salesforce Contact Id, this can make it really quick.

You can also refer:

http://www.allaboutsfdc.com/vlookupfunction.php

http://gokubi.com/archives/vlookup-new-to-me

http://www.youtube.com/watch?v=aThWOVtjYNI - Video example

http://stackoverflow.com/questions/10951756/vlookup-salesforce

I hope it helped, if so please mark it as the answer.

Regards;
Amit Gupta

Deepak AnandDeepak Anand
Hey Arpita,

Forget about the Zip Code example. Lets take an another one for now.

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:
  1. 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.
  2. 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 the Name 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:
  1. VLOOKUP only available on Custom Objects. Vote for this idea: https://success.salesforce.com/ideaView?id=08730000000BqPs
  2. VLOOKUP only available in Validation Rules.
  3. VLOOKUP can only be done on the Name fields.
  4. 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 URL field type.
  5. The field_on_lookup_object must be the Record Name field on a custom object.
  6. The field_on_lookup_object and lookup_value must be the same data type.

Hope this helps!

Best Wishes,
Deepak
 




This was selected as the best answer
Rahul ChauhanRahul Chauhan
Thanks a lot  Mr.deepak
Rahul ChauhanRahul Chauhan
Mr.deepak if i want to try using vlookup then how can i implement it ?as i just want tor try how it works
Raja . YRaja . Y
hi Rahul ,here is the the example of VLOOKUP () function

AND(
OR(
ISNEW(),
ISCHANGED( Name )
),
NOT(
ISBLANK (
VLOOKUP( $ObjectType.AutoResponder__c.Fields.Name, $ObjectType.AutoResponder__c.Fields.Name,  Name )
)
)
)

----------------
Create one new object named as Autoresponder, and take the field as name.
go to validation rule for the object . copy the code and then test it
ranadheer reddy chaduvuranadheer reddy chaduvu
this s good to make name field of custom object unique...thanks
Reetan NayakReetan Nayak
Hi Deepak Anand,

Can u pls help me here on below Q's ?
How to implement Vlookup on Custom Field ?

I have a Report fetching the records from 3 Objects (Opportunity, Product2, OpportunityLineItem)

Q1. Need the Opportunity ID, where the Product Family= "Services", "Solutions" (Product Family is a Picklist field on Product2 Obj). See the Formula below.
Opty_PROD__c = IF( OR(ISPICKVAL(PricebookEntry.Product2.Family, 'Services'), ISPICKVAL(PricebookEntry.Product2.Family, 'Solutions')), Opportunity.Id , '')

Q2. Map all the Opportunity ID's in the in the above formula Opty_PROD__c.
If it is Match assign '0',
Else '#NA'

Could you please help me how to implement No# Q2.

Thanks,
Reetan (reetan.adc@gmail.com)
 
Derhyk DoggettDerhyk Doggett
To those looking for VLOOKUP functionality to populate a field, I managed to get behaviour similar to excel VLOOKUP via apex trigger.

Example below.

The example uses Product_Code__c on Asset History object to get the Product Description from the Product object. It was not acceptible to traverse Asset to get the Product details from the Asset as it could be different per Asset History Entry.

trigger productPopulate on Asset_History__c (before insert, before update) {

    Map <String,String> ProductMap = new Map <String,String>();

    List<Product2> ProductList = [Select ProductCode, Name from Product2];

    for (Product2 p : ProductList){
        ProductMap.put (p.ProductCode, p.Name);
    }

    for (Asset_History__c ah : Trigger.new){
        ah.Product__c = ProductMap.get(ah.Product_Code__c);
    }
}
Karthik TrainingKarthik Training
Hi All,

We can Use VLookup for Standard objects also.

Thanks
Karthik
Jean-Luc SchellensJean-Luc Schellens
Hi All,
Based on the different Q&A above, I would like to submit the following use case.

I created two custom objects:
1° A Campaign Channel where the user has
- to select in a picklist a specific channel e.g. email, SMS, phone
- to define the expected response rate - a percent - of the selected channel
2° A Channel Reference - in read only mode - where for each channel in the picklist the related response rate is defined

Like in Excel, I would like to use a VLOOKUP to automatically populate the response rate from the Channel Reference object into the Campaign Channel object.

Is it possible with a validation rule or does it requires an apex dev?
Thanks already for your answers.
Regards
Jean-Luc
Manish KumarManish Kumar

Hi All,
i tried to run this code but its executing every time so can't able to save a record.what could be the problem....?
Name = VLOOKUP( $ObjectType.Speaker__c.Fields.Name,$ObjectType.Speaker__c.Fields.Name , Name)

Thanks,


 

Anirudh BoraAnirudh Bora
how can we implement this logic of unique candidate using workflow?
please ASAP
Susan HarrisSusan Harris
Hopefully, better late than never here. Without having to use code, you can use Lookup Helper to automatically populate lookup fields. You can install it free from the AppExchange: https://goo.gl/LvaVlk

Full Disclosure: I work for Passage Technology, the makers of Lookup Helper.
Irina ClingtonIrina Clington
Hi Deepak Anand,
I'm trying to implement your awesome validation rule with the VLOOKUP and although it works in the GUI I get  MESSAGE 
System.DmlException: Insert failed. First exception on row 0; first error: FIELD_CUSTOM_VALIDATION_EXCEPTION, Validation Formula "Unique_Api_template_name" Invalid (common.exception.SfdcSqlException: ORA-01795: maximum number of expressions in a list is 1000

//validation rule AND( VLOOKUP($ObjectType.ApiTemplate_c.Fields.Id, $ObjectType.ApiTemplate_c.Fields.Name, Name) <> Id, OR( ISNEW(), ISCHANGED(Name) ) )
Hope you or anyone else can help me
Anmol MAnmol M
Thanks @Deepak Anand. Making name field unique is basic stuff any newbie would look for in salesforce
Bill RiemersBill Riemers
@Deepak, that is an excellent example.  Which is why I recommend removing it and replacing it with something better...

You see it is just good enough for users to understand how VLOOKUP works, but not enough to realize the dangers inherent with actually implementing this example as is.

If you review the order of operations:

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_triggers_order_of_execution.htm

You'll observe validation is done very early on in the processing of a record.   Now just imagine if two records are created at the same time.   Neither one is going to show-up in the other's vlookup results yet, so the validation step will pass.  You'll end-up with two records of the same name, dispite the validation rule.   If you don't think this can happen, custom settings are suppose to be required to have unique names.   In our organization we use custom settings to selectively activate or deactivate triggers in real time.   At some point I had the bright idea to add code, that if a custom setting didn't exist auto-create it.   The net result is quite common for us deploy new code, and within minutes find several duplicates of the same custom setting, created by different USER's that invoked the triggers at the same time.   Like the VLOOKUP example, there is no locking done to achieve the uniqueness criteria of custom settings.   It used to be a single duplicate custom setting break the whole custom setting table, but since then salesforce patched their code so only the duplicate setting is broken.

So in your example, invariably you are still going to end-up with duplicate records, despite the validation rule.   Should there be heavy usage of the respective object.

The best way I've found to make sure "Name" values are unique in salesforce, is you add a trigger or workflow rule that copies the name to unique external id field.   Salesforce will actually throw a locking error rather than allowing the creation of a duplicate unique external id field, so it is much safer way to do things.
 
Nicole KNicole K
following @Deepak Anand (thank Deepak - your answer helped a lot),
I suggest a modification -
Id != VLOOKUP(
    $ObjectType.Employee__c.Fields.Id,  
    $ObjectType.Employee__c.Fields.Name,  
    Name)

Motivation:
Deepak's validation didn't work for me for updates - it will always fail an update, since VLOOKUP finds the records being updated.

Solution explained:
My modification comes to deal with that, by comparing ID's -
  1. for a new record, the record Id would be null at validation time, so VLOOKUP would look for a record with id that is not null
  2. For an existing record, both edited and other existing records have ID's - and the VLOOKUP condition will fail the validation only if it finds a different id, meaning a different record.

 
Larissa JohnsonLarissa Johnson
This was a huge help to me! Exactly what I needed as I was stumped trying to solve a similar use case on my own. Thank you @Deepak Anand!
Doug HauckDoug Hauck
Nicole K - I ran into that "object, meet thyself" problem the very first time I tried Deepak's original solution.  But your mod seems to have patched it up a treat.  Thanks to both of you!