Skip to main content

Description

Searches a value in a Dimension List property, either unique or non-unique, and returns the first item matching the value and  BLANK if none is found.

 

Unlike the MATCH function in Excel, the MATCH function in Pigment is case sensitive. 

The following functions work effectively with MATCH:

  • LOWER. Convert a Text value to lowercase. For more information, see LOWER Function.
  • TRIM. Removes leading and trailing spaces and replaces multiple spaces between words with a single space. For more information, see TRIM Function.

Here's how you'd use the LOWER and TRIM functions with MATCH:

MATCH(TRIM(LOWER(Load.Email)), TRIM(LOWER(Dimension.Email)))

 

Syntax

MATCH(Value to Match, Expression)

  • Value to Match can be any expression, that is, a string, number, Metric, List, or List Property. It can be made up of the following data types:  text, number, integer, or Dimension.

  • Expression can be a Metric, List, or List Property, and doesn’t need to contain unique values. If Expression is a Metric, then it needs to be defined on only a single list in the Metric structure.

  • Expression and Value to Match need to be the same data types. 

  • The order for the first match found for ValueToMatch is determined by the ordering of the items in the Expression.

Returns

Type Output Structure (Dimensions)
Dimension (same as Expression)

Same as Value to Match (if any exists, else will return Dimension Item of Expression)

 

 

Examples

Case

Results

Return Type

MATCH("john@corp.com", 'Employees'.'Email')

Returns "John H.", item of the Employees List. This is returned because it’s the first match found in the employee email list.

Employee (Dimension)

MATCH("unknown@corp.com", 'Employees'.'Email')

Returns blank if the email is not recognized in the List.

Employee (Dimension)
MATCH(City.'Duplicate countries',Country.Name) For each row of the Duplicate Countries text property in the City List, it looks for the first matching country by name in the Country List, and then returns the first match found as an item of Country.  Country (Dimension)

 

Excel equivalent: Similar to VLOOKUP, where vrange_lookup] is FALSE

See also: ITEM

Be the first to reply!

Reply


Ask your question here!

Terms & ConditionsCookie settings