Using VLOOKUP function in validation rules.

VLOOKUP function 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)

Example:

Create validation rule that checks the Billing ZIP/postal code entered in an Account 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(as a Standard Name Field), State(as State__c Custom Field) And Created below records (sample records) in this Zip Codes object.

See the below image for a record reference in Zip Code Object.

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:

UPPER(VLOOKUP($ObjectType.Zip_Code__c.Fields.State__c,$ObjectType.Zip_Code__c. Fields.Name,BillingPostalCode))<>UPPER(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 State/Province = AP and postal code =524000 and save this record, you will get associated error message. If you enter correct data which is mentioned in above Zip Code record, record will save. If you enter wrong data validation rule will fire an error.

See the below image for reference.