A useful and often overlooked Salesforce function VLOOKUP explained. Admin#1

VLOOKUP is a very powerful function in salesforce which can be used to improve data quality in the org and provide a robust way to validate data entered by the user against the data, stored in the object, without writing a single line of code. In order to use VLOOKUP, one does not need any specific skill (admin or dev) and it can be configured very quickly.

There are many possible use cases for VLOOKUP in salesforce. For example, if you want to validate zip code entered by the user or if you want to validate the ISD code or area code etc. entered in the phone number field, etc..

VLOOKUP function in salesforce is very similar to that in Excel. It takes data which user enters on UI and use it to look up something else and compare.?Lets see with an example -

In this article, I would like to explain this with the help of a very simple zip code validator.

In my use case, my company deals with suppliers located in three states in the United States. (NJ,NY,AZ). For that, I need to create a screen to fill supplier data on the screen. I have created a small screen. See below.

In this underlying object I would also like to create a validation rule to validate zip code. Entered zip code should be a valid zip code and belong to the entered city. If the input value of zip code does not matches with city name then we should throw a validation error.

In order to achieve this, first I have to build my database for the zip code for these three states. I have downloaded all possible zip code along with their cities from a free public web sites. I have also created a custom object and uploaded data in that custom object for the zip code with the help of data loader. So basically I have now my database ready for zip code. User will enter zip code and city on the screen and that value will get validated against this object. My zip code data looks like as below -

Ok, so my screen is ready and all possible zip codes database is ready. Now as a next important step, I need to validate the entered zip code and need to make sure that zip code belongs to the city.

Of course, I can write triggers or any other programmatic approach for the same. But we will keep it simple and use validation rule from the admin tool. We can use VLOOKUP functions in formula field.

Syntax - VLOOKUP(field_to_return, field_on_lookup_object, lookup_value)?

In my case, I have used the following formula in the validation rule -

Explanation -

1st parameter (field_to_return) - In the 1st parameter, you need to enter the element which you want to return as a result of VLOOKUP function.

In my case as you can see above, I would like to return the value from City field (API Name - City__c) from my lookup object (Object API Name - Zip_Code__c).

2nd Parameter(field_on_lookup_object) and 3rd Param (lookup_value)

2nd and 3rd params will be from the list/range you want to lookup and the element

(value) you want to lookup respectively.

2nd parameter will be the field name from custom object(its a list in which we would like to search) and the 3rd one will be the value from UI field which we want to search.

So in this case, this VLOOKUP function would lookup from all the values stored in the field city__c stored in a custom object (Zip_code__c) and would search value of the field zip_code__c from UI. If the value matches, it will return the value present in a field (City__c, which is 1st param) from the same object.

In the validation rule , I am comparing the return value from the VLOOPUP function to the value entered in the UI field. If it does not match , of course validation failed and if it matches, validation passed.

Thank you for reading!