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
    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
    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
    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
    Jun 2, 2014 at 19:18
  • Ok, back to the drawing board. Thanks for your help. I appreciate it!
    – jeffro25
    Jun 2, 2014 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.