I'm trying to populate a field on the leads object with data from a custom object via a formula field. I'm pretty close to getting the right stuff there, but I am getting a little hung up and wondering if anyone out there has a solution.

I'm retrieving the data via VLOOKUP, but I keep getting syntax errors. I have create the relationship with the leads and custom object, which is labeled Market__c. The field i am trying to retrieve data from is a picklist and it keeps telling me that "Error: Field Market_Status__c is a picklist field. Picklist fields are only supported in certain functions." The picklist only has 2 options, open and closed. Thats a pretty obvious explanation as to why it won't work, but I am looking for the value of the field, not the picklist values.

In the code below, Market__c is the field from the lead that I am passing over as the value to search for in the Market Object. It should match the field Name in the Market object and return the value of the field Market_Status__c associated with the matched Name.(For instance Market__c = Miami: FL. it matches the name in the Market object and finds whether the market is open or closed. That data is contained in the Market_Status__c field.)

AND(LEN(Market__c) > 0,
VLOOKUP( Market_Statuses__r.Market_Status__c ,  Market_Statuses__r.Name , Market__c ))

Anyone have any idea what I could be doing wrong?

1 Answer 1


If I understood correctly, this might help:

AND(LEN(Market__c) > 0, VLOOKUP( TEXT(Market_Statuses__r.Market_Status__c) , Market_Statuses__r.Name , Market__c ))

  • You understood perfectly! however I am getting a different error now. "Error: Function VLOOKUP may not be used in this type of formula". It seems i am using VLOOKUP correctly, but it's still throwing errors.
    – jeffro25
    Commented Jun 2, 2014 at 19:05
  • According to the salesforce docs VLOOKUP may only be used in validation rules. So you cannot use it in a formula field. Is 'Market__c' a text field on the 'Lead' object - or is it a relationship to your custom object?
    – Christoph
    Commented Jun 2, 2014 at 19:12
  • Is there any way to use this method to retrieve the value? Market__c is a text field that will contain the market that the customer is in.
    – jeffro25
    Commented Jun 2, 2014 at 19:16
  • According to the docs, no. Do you have a direct link between your lead and your Market object?
    – Christoph
    Commented Jun 2, 2014 at 19:18
  • Ok, back to the drawing board. Thanks for your help. I appreciate it!
    – jeffro25
    Commented Jun 2, 2014 at 19:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .