Ah, the VLOOKUP. Salesforce has a lot of different formula functions, and this one may be the trickiest. Even in my experience, I haven’t had many chances to make good use of it. So what does VLOOKUP do? And how can you use it to enhance your org?
VLOOKUP lets you find data on a custom object in a formula. It takes three arguments: The first is which field from the custom object to return, the second is which field on the custom object to search by, and the third is the search key. This may be easier to think of in reverse: Take a field on your current object, search a custom object for a record which has a name that equals that field, and return a different field on that custom object.
Now, this power comes with its share of constraints, the biggest being that you must be working in a validation rule when you use VLOOKUP. Salesforce also dictates that you can only look up custom objects, not standard ones. These are some pretty serious limitations, but they’re also very informative. They give us a major clue into what the developers had in mind when they created this in Salesforce. In short, VLOOKUP is built to let you validate data against a known list of values. It helps ensure that two fields on your record have compatible data.
So what would you use VLOOKUP for? The most common example is making sure the correct state code is chosen for a given ZIP code. But you can also use it to validate that a particular dosage is available for a given medicine. Or you can use it to enforce unique record names on custom objects, or (with a little trigger or process builder magic) even for standard ones. You could even use it as a workaround for other Salesforce limits, like creating a dependent picklist with too many dependent values for a normal implementation. Any time you have a complex data validation need, VLOOKUP can be a powerful tool in your arsenal.
Here’s a recent example of my own: We have a client who’s using a custom object to handle many-to-many Contact and Account relationships. Their Contacts can have different roles at multiple Accounts, and the native Contact Roles functionality wasn’t sufficient for their needs. One of the things that they wanted to track was effectively a supervisor structure: “Which of these Contacts is reporting to another Contact?”, in the context of these custom roles. By using a VLOOKUP Salesforce validation rule, we were able to make sure that users could only select supervisors that were at the same Account as the Contact that they’re currently working with.
Got questions about VLOOKUP? Have an example of usage that you’re particularly proud of? Let us know via social media or in the comments below.
