Other Extension Functions

The VLOOKUP function allows you to find the value of a matching record by looking up the values on the sheet.

The VLOOKUP function is an extension function added in the Mashmatrix Sheet formula. It is not compatible with the VLOOKUP function available in Salesforce validation rules..

VLOOKUP(value, search_column, result_column)

Searches for a record that matches a specified value and returns the value of that record.

Argument Name

Description

value

value to search for a match

search_column

Reference to the column with the value to search for a match

result_column

Reference to the column with the value to be returned

Formula Examples

/* Search for the record in sheet "s1" whose "ProductId" column value matches
   the ProductId value of the current record, and returns the "ListPrice"
   column value of that record */
VLOOKUP(CASESAFEID(ProductId), [s1].[#ProductId], [s1].[#ListPrice])

/* Searches for the record in sheet "s1" whose "Name" column value matches the
   concatenation of the "Name" and "Type" column values of the current record,
   and returns the value of the "Amount" column of the record */
VLOOKUP([@Name] + " - " + [@Type], [s1].[#Name], [s1].[#Amount])

VLOOKUP Function Limitations and Notices

The VLOOKUP function searches only for records retrieved on the sheet, not entire records stored in the Salesforce object. You can increase the number of records to be searched by changing the "Max Fetch Record Num" from the sheet settings.
Field variables cannot be set in the search and result column arguments of the VLOOKUP function. To search for a Salesforce field, you must first add the field as a column to the sheet, and then pass the added column to the VLOOKUP function.
The search and result column arguments of the VLOOKUP function must be columns that belong to the same sheet.
When specifying an field variable of the ID type as the value of a match search, it is necessary to use the CASESAFEID function to unify the 18-digit notation.

PreviousAggregation Functions NextReference Value

Last updated 3 years ago

/* Search for the record in sheet "s1" whose "ProductId" column value matches the ProductId value of the current record, and returns the "ListPrice" column value of that record */ VLOOKUP(CASESAFEID(ProductId), [s1].[#ProductId], [s1].[#ListPrice]) /* Searches for the record in sheet "s1" whose "Name" column value matches the concatenation of the "Name" and "Type" column values of the current record, and returns the value of the "Amount" column of the record */ VLOOKUP([@Name] + " - " + [@Type], [s1].[#Name], [s1].[#Amount])

VLOOKUP Function Limitations and Notices

The VLOOKUP function searches only for records retrieved on the sheet, not entire records stored in the Salesforce object. You can increase the number of records to be searched by changing the "Max Fetch Record Num" from the sheet settings.

Field variables cannot be set in the search and result column arguments of the VLOOKUP function. To search for a Salesforce field, you must first add the field as a column to the sheet, and then pass the added column to the VLOOKUP function.

The search and result column arguments of the VLOOKUP function must be columns that belong to the same sheet.

When specifying an field variable of the ID type as the value of a match search, it is necessary to use the CASESAFEID function to unify the 18-digit notation.