Salesforce VLOOKUP Function

The Salesforce VLOOKUP function is used to search and retrieve data from related records within Salesforce.

This is one of the most complicated functions in Salesforce; new learners and even developers are trying to avoid this for use, but the function has lots of good features.

The VLOOKUP function is used to apply where you want to apply high security. It restricts access to sensitive information and maintains data privacy.

If you want to learn it in a simple and quite easy way, don’t worry. I am here, your experienced Salesforce administrator with teaching experience.

In this Salesforce Tutorial, I will deelply explain about the VLOOKUP function in Salesforce. I will also explain the syntax, use cases, considerations, and many more.

Additionally, I will also show you a step-by-step implementation of how to use the VLOOKUP function in Salesforce Lightning and Salesforce Classic with examples in validation rules.

VLOOKUP Function in Salesforce

What is the VLOOKUP Function in Salesforce?

The VLOOKUP function in Salesforce is one of the advanced Salesforce validation functions. The VLOOKUP stands for Vertical Lookup.

It works similarly as it works in Excel and is commonly used in spreadsheet tools like Excel.

The function searches for a value in the first column of a table array or columns of the object database and returns a corresponding value from a specified column.

If talk about Salesforce, the VLOOKUP function is used to retrieve data from related records based on a matching criterion. It searches an object for a record where the specified field matches the specified lookup value. If a match is found, it returns another specified field value.

What is the syntax of the VLOOKUP Function in Salesforce?

The following is the syntax to write the VLOOKUP function in Salesforce:

VLOOKUP(field_to_return, field_on_lookup_object, lookup_value)

The function takes three parameters only, which are defined as follows:

field_to_return: The field_to_return parameter specifies the field from the related object that contains the value you want to be returned. In simple words, when the lookup finds a match, it will return the value from this field.

Example: You want to look for the product price field to return will be the price.

field_on_lookup_object: The field_on_lookup_object parameter specifies the field on the related object where the lookup value will be searched. It is a field that Salesforce will use to search for a match.

Example: Suppose you are trying to match a product name, and the name is stored in the product name field in the related object; so here, you’d specify the product name.

lookup_value: The lookup_value parameter is the value you want to match. It is the value that you want to search within the field on the lookup object.

Example: It is the name of the product you’re interested in fetching the price for.

Are there any considerations of the VLOOKUP Function in Salesforce?

There are various considerations that you must take care of while working with the VLOOKUP function in Salesforce:

  • The field_to_return parameter supports various field types, including auto number, roll-up summary, lookup relationship, master-detail relationship, checkbox, date, date/time, email, number, percent, phone, text, text area, or URL.
  • The field_on_lookup_object parameter takes only the custom object Record Name field. For objects, Salesforce has a standard field called Record Name, which is used to hold a unique name or identifier for each record. This guarantees that every record in the linked object has a unique identifier that serves as the basis for the lookup operation.
  • The parameter lookup_value must be of the same data type as the field_on_lookup_object. It enables accurate matching during the lookup process.
  • If there are multiple records in the related object that match the lookup_value, the VLOOKUP function returns the value from the first record it encounters.
  • The VLOOKUP function returns values from custom objects in Salesforce. It cannot be used to retrieve values from standard objects. In simple words, the value returned must be on a custom object.
  • The custom field or custom object is referenced in a VLOOKUP function; it cannot be deleted without first removing the reference from the function.
  • The VLOOKUP function is only available in the Salesforce validations rule, which means you can’t use it in the formula field, workflow rules, field updates, etc.

Use case of the VLOOKUP Function in Salesforce?

Suppose you want to validate the discount coupons on the order object using the VLOOKUP function in Salesforce.

You have a custom object named “COUPON” with fields such as “Coupon Name” (Record Name) and “Coupon Type”. The “Coupons Name” field uniquely identifies each coupon, and the “Coupon Type” specifies the type to which this coupon belongs.

See also  CASE Function in Salesforce

Now, you want to use this coupon on the “ORDER” object. You want to validate that the “Coupon Type” field matches the type of the “Coupon Code” field.

For example, if a person wants to apply the coupon while ordering at that moment, the VLOOKUP function validates the coupon code with its type.

If the coupon code entered by the user is “48956” and the coupon type is “Food Coupon”, the validation rule created by using the VLOOKUP function will check the coupon code and coupon type in the coupon object. If it matches it, apply the coupon; otherwise, raise an error.

Let’s understand which field you fill up in which parameter of the function:

  • field_to_return: Here, fill up the reference of the coupon type field of the coupon object.
  • field_on_lookup_object: Here, fill up the reference of the coupon name field of the coupon object.
  • lookup_value: Here, fill up the reference of the coupon code field of the order object.

With this, you have learned the syntax, use case, and consideration of the VLOOKUP function in Salesforce.

How to use the VLOOKUP Function in Validation Rule in Salesforce Lightning

Let’s learn to work with the VLOOKUP function in the validation rule in Salesforce Lightning.

Consider a scenario where you are working in a product-selling organization that sells various products, and to manage the product inventory, you are using the Salesforce CRM system.

You want to create a validation rule in Salesforce that compares the “Product Category” of a “Product Code”; if they are not equal, you want to trigger an error.

The steps are given below to achieve the scenario using the VLOOKUP function.

1. Login into the Salesforce account as an administrator in the Lightning Interface. Mostly, you are on the homepage or dashboard. To redirect to the setup option, look for the “Gear Icon” in the top-right corner of the screen.

Once you click on it, you will get the dropdown menu, and from the menu, select “Setup”.

2. To use the VLOOKUP function, first, you need to create the custom object on the name of the field where you want the lookup value to be searched because you want the create record name field that creates a unique identifier that serves as the basis for the lookup operation.

According to the scenario, I want the “Product Code” field to uniquely identify each product, so I have created a custom object named “Product Code”.’

VLOOKUP Function in Salesforce Lightning

If you don’t know how to create a custom object in Salesforce, visit the tutorial: “How to Create a Custom Object in Salesforce

Note: Make sure to set the Record Name field as “Product Code”.

3. After creating the object, create a custom field. According to the scenario, you need to create a field named “Product Category”.

Take care while choosing the data type; choose the “Text” data type, as the “Picklist” data type would not work in most of the functions.

VLOOKUP Function in Salesforce Lightning Example

If you don’t know how to create a custom field in Salesforce, visit the tutorial: “How to Create Text Field Type in Salesforce

4. Next, add up the records in the “Product Code” object either manually or with the help of the data loader.

If you don’t know how to insert a record in Salesforce manually or with a data loader, visit the tutorial: “How to Create Records in Salesforce“.

Example of VLOOKUP function in Salesforce Lightning

5. Now, choose the object on which you apply the validation rule. Locate and click on the “Object Manager” tab.

Choose the object for which you want to create the validation rule. For example, according to the scenario, you need to create a validation rule for the product object, so search and click on “Product”.

Salesforce VLOOKUP Function

6. In this step, you will be on the object management page. Look for the “Validation Rules” in the left sidebar menu and click on it.

Then, on the validation rule page, you will get the “New” button near on right top of the page. Click on it to start creating a new validation rule for the selected object.

Salesforce VLOOKUP Function Example

7. Enter the “Rule Name” and “Description” for the validation rule in the respective fields. Next, make sure to check the “Active” checkbox to enable the validation rule. If this checkbox is not checked, the validation rule will not work.

In accordance with the scenario, enter the descriptive rule name as “Product Category Verification“.

Salesforce Lightning VLOOKUP Function

8. Then, in the “Error Condition Formula” section, define the criteria that must be met for the validation rule to trigger. The formula will evaluate the validation rule to be true or false.

Use the formula editor to create the formula by following the below-given steps:

  • Navigate to the “Function” section on the right side of the page.
  • Then, click on “All Function Categories” dropdown.
  • Choose the category from the list. According to your scenario, choose “Advance”.
  • Select the function from the list of functions under the specific section. According to your scenario, select the “VLOOKUP”.
  • Click on the “Insert Selected Function” button located under the functions box.
See also  CHR Function in Salesforce

According to your scenario, replace the parameters of the function with fields, operators, or functions required. The formula is as given below:

VLOOKUP( $ObjectType.Product_Code__c.Fields.Product_Category__c ,            $ObjectType.Product_Code__c.Fields.Name , ProductCode ) <> Product_Category__c

A detailed description of the formula is given below:

  • VLOOKUP Function: It is used to retrieve data from related records where the specified field matches the specified lookup value based on matching criteria.
  • $ObjectType.Product_Code__c.Fields.Product_Category__c: It refers to the Product_Code__c field of the Product_Category object using dynamic reference notation in Salesforce formulas. It is a field from the lookup object that you want to return.
  • $ObjectType.Product_Code__c.Fields.Name: It refers to the Name field of the Product_Code__c object using dynamic reference notation. It is a field on the lookup object where the lookup value will be searched.
  • ProductCode: It refers to the ProductCode field of the “Product” object. It is the value being looked up in the field on the lookup object.
  • <>: This is the “not equal to” operator in Salesforce formulas.
  • Product_Category__c: It is a field of the “Product” object.

After inputting the formula, click the “Check Syntax” button to see if it is correct or if there are any errors. If there is no error, you will get the message “No errors Found”.

Salesforce Lightning VLOOKUP Function Example

9. In the “Error Message” enter an error message. The message will be displayed to users when the validation rule is triggered; it explains how to resolve the issue.

According to your scenario, you can add the error message as given below:

Product Category does not match the category derived from the Product Code.

Next, under the “Error Location” section, you have the option to select where the error message should be shown.

  • If you check the “Field” radio button, you can opt to see the error message in the field itself.
  • By selecting the “Top of Page” radio button, you can decide whether to show the error notice at the top of the page.

10. Once you have configured all the settings for your validation rule, click on the “Save” button to create the validation rule.

VLOOKUP Function in Salesforce

11. It is important to verify the validation rule after saving it. To do this, create or edit a record on the “Product” object. When you save the record, the validation rule will be triggered.

If the validation rule conditions are met, the error message you specified should be displayed. Let’s understand with an example:

Suppose you have created the product with the “Product Code” and “Product Category” fields with values “P100” and “Electronic”, respectively, and try to save it.

But in the “Product Code” object, the “Product Code” of the “Electronic” “Product Category” is P112.

Hence, the product category field is compared with the category derived from the product code, and it is found that it does not match, so the validation rule gets activated and triggers an error.

VLOOKUP Function in Salesforce Example

With this, you have learned how to use the VLOOKUP function and create a validation rule that ensures the product category specified for each product is accurate and consistent with the product code.

How to use the VLOOKUP Function in Validation Rule in Salesforce Classic

Let’s consider a scenario to understand the use of the VLOOKUP function in Salesforce.

Scenario: Suppose you are working in an e-commerce company, and to ensure efficient delivery management, the company has a “Postal Code” object in its Salesforce database. In this object, the company stores all postal codes where delivery is available.

You want to create a validation rule that checks whether the postal code entered in an “Orders” object record is available in the “Postal Code” object.

If the postal code is available, no validation is triggered, showing that delivery is possible. However, if the postal code is not found in the “Postal Code” object, a validation error should occur, indicating that delivery is not available to that location.

The following are the steps to use the VLOOKUP function in the validation rule in Salesforce Classic.

1. Log in to Salesforce as an administrator in the Classic interface. Navigate to the setup page by clicking on the “Setup” option given at the top right of the page between your name or help option.

VLOOKUP Function in Salesforce Classic Example

2. Create a custom object named “Postal Code” with a unique identifier field called “Postal Code”. This field will serve as the basis for the lookup operation.

Salesforce Classic VLOOKUP Function
Salesforce Classic VLOOKUP Function Example

3. Populate the “Postal Code” object with records, either manually or using the Salesforce Data Loader tool.

Example of VLOOKUP Function in Salesforce

4. Navigate to the “Build” section on the left sidebar under the “Customize” dropdown menu, and select the object on which you want to create the validation rule.

See also  LOG() Function in Salesforce | Calculate logarithmic value in Salesforce

In this scenario, choose the “Orders” object.

After that, locate and click on the “Validation Rules” link to access the validation rules page.

VLOOKUP Function in Salesforce Classic

5. Click on the “New” button to start creating a new validation rule. Here, you need to define the validation rule and the error condition with the error message.

VLOOKUP Function in Validation Rule in Salesforce Lightning Example

6. Now, enter the “Rule Name” and “Description” as per the requirement. Then, ensure that the “Active” checkbox is checked to activate the validation rule.

In this scenario, you can input “Delivery Availability” as the rule name.

VLOOKUP Function in Validation Rule in Salesforce

7. In the “Error Condition Formula” field, enter a formula that evaluates to true when the validation rule should fire.

To create the formula for the validation rule, navigate to “All Function Categories” -> select the appropriate “Function Category” -> choose the desired “Function” -> then click “Insert Selected Function” to add it to the formula expression box.

Once the function is added, replace the parameter with appropriate fields, functions, and operations.

To enter the fields that are linked to related objects, click on the “Insert Field”, -> then click on the “$ObjectType”, -> after that click on the object -> select the field that you require.

According to the scenario formula is given below:

VLOOKUP( $ObjectType.Postal_Code__c.Fields.Name ,  $ObjectType.Postal_Code__c.Fields.Name ,  Postal_Code__c ) <> Postal_Code__c

Here is a detailed explanation of the formula:

  • VLOOKUP: The function is used to fetch data from related records where a specified field matches a designated lookup value based on defined matching criteria.
  • $ObjectType.Postal_Code__c.Fields.Name: This references the Name field of the Postal_Code__c object using dynamic notation within Salesforce formulas. It denotes a field from the lookup object that you aim to retrieve.
  • $ObjectType.Postal_Code__c.Fields.Name: It specifies the Name field of the Postal_Code__c object using dynamic reference notation. This field is where the lookup value will be searched.
  • Postal_Code__c: Represents the Postal_Code__c field of the “Orders” object. It is the value being searched in the field on the lookup object.
  • <>: Denotes the “not equal to” operator in Salesforce formulas.
  • Postal_Code__c: Refers to a field within the “Orders” object.

To verify the accuracy of the formula, click on the “Check Syntax” button. If the error is present, you will get the error message.

VLOOKUP Function in Validation Rule in Salesforce Example

8. Enter the error message in the “Error Message” box. This message will appear when the Error Condition formula is true. According to the scenario, the error message is as follows:

Order Delivery is not available on this Postal Code.

This error message can either appear at the top of the page or below a specific field on the page. Choose your desired location from the “Enter Location” field.

You will get two options there: “Top of Page” or “Field”. Select any one of them by clicking on the radio button.

9. To save the validation rule, click on the “Save” button.

How to use VLOOKUP Function in Salesforce

10. Verify the validation rule after saving it. To do so, create or modify a record in the “Orders” object, and on saving the record, the validation rule will be invoked.

If the conditions specified in the validation rule are fulfilled, the defined error message will be presented. Let’s illustrate this with an example:

  • According to our records, a customer is placing an order and enters a New York postal code, “10001”, that is not available for delivery.
  • When he tries to save the order, he will get the error “Order Delivery is not available on this Postal Code” since the postal code entered is not found in the “Postal Code” object.
How to use VLOOKUP Function in Salesforce Example

With this, you have learned how to create the validation rule in Salesforce Classic with the VLOOKUP function.

Conclusion

To return a value by looking up a related value on a custom object in Salesforce, the VLOOKUP function is there. It improves the data quality in the org.

There are multiple use cases of the VLOOKUP function that you can use in the validation rule in Salesforce. It retrieves the data entered by the user on the user interface and utilizes it to perform a lookup operation, comparing it with other data.

You will learn through the article how to use the VLOOKUP function in the validation rule in Salesforce Lightning and Salesforce Classic step-by-step.

You may like to read: