Using VLOOKUP function in validation rules
What is the purpose of VLOOKUP function? It searches an object for a record where specified field matches the specified lookup value. If a match is found, returns another specified value.
Syntax of vlookup:
VLOOKUP(field_to_return, field_on_lookup_object, lookup_value)
Here I will give a simple example, how to use VLOOKUP function in validation rule.
Example: Create validation rule that checks the “Billing ZIP/postal code” entered against a table to validate that the zip code and state match.
To track ZIP Codes I have created custom object called “Zip Code” in this object I am storing zip code, states & countries. And created below records (sample records) in this zip codes object.
| ZIP Code | State | City |
| 35004 | Alabama | Moody |
| 35005 | Alabama | Adamsville |
| 35010 | Alabama | Alexander City |
| 90001 | California | Los Angeles |
Below validation rule validate Billing zip/postal code in account object with the codes in zip code object. If you enter wrong zip code this validation rule throw an error.
Creating validation rule:
To create validation rule go to setup -> Build -> Customize -> Account -> Validation rule and enter required information.
Error condition formula:
VLOOKUP($ObjectType.Zip_code__c.Fields.State__c, $ObjectType.Zip_code__c.Fields.Name , LEFT( BillingPostalCode ,5))<> BillingState
See the below image for reference.
How to test this validation rule?
To test this validation rule go to Account tab and create new account record with enter billing city = Los Angeles, Billing State/Province = Califirnia and postal code = 3099 and save this record, you will get associated error message. If you enter correct data which is mentioned in above table, record will save. If you enter wrong data validation rule will fire an error.
See the below image for reference.
