You are here:
VLOOKUP
Returns a value by looking up a related value on a custom object similar to the VLOOKUP() Excel function. This function is only available in validation rules.
Use
VLOOKUP (field_to_return, field_on_lookup_object, lookup_value). Replace field_to_return with the field that contains the value you want returned, field_on_lookup_object with the field on the related object that contains the value you want to match, and lookup_value with the value you want to match.
Tips
- field_to_return must be an auto number, roll up summary, lookup relationship, master-detail relationship, checkbox, date, date/time, email, number, percent, phone, text, text area, or URL field type.
- field_on_lookup_object must be the Record Name field on a custom object.
- field_on_lookup_object and lookup_value must be the same data type.
- If more than one record matches, the value from the first record is returned.
- The value returned must be on a custom object.
- You can’t delete the custom field or custom object referenced in this function.
This example checks that a billing postal code is valid by looking up the first five characters of the value in a custom object called Zip_Code__c that contains a record for every valid ZIP code in the US. If the ZIP code isn’t found in the Zip_Code__c object or the billing state doesn’t match the corresponding State_Code__c in the Zip_Code__c object, an error displays.
AND(
LEN(BillingPostalCode) > 0,
OR(BillingCountry = "USA", BillingCountry = "US"),
VLOOKUP(
$ObjectType.Zip_Code__c.Fields.State_Code__c,
$ObjectType.Zip_Code__c.Fields.Name,
LEFT(BillingPostalCode,5)
) <> BillingState
)
- Use this example when the billing country is US or USA.
- You can download US ZIP codes in CSV file format from http://zips.sourceforge.net.
