LOOKUP function

Synopsis

This function uses an array as a lookup table and returns an index or a result.

Variations

LOOKUP(SourceValue, LookupArray, MatchType)

Returns the index of the value in the position lookup array closest or equal to the value specified, depending on the match type.

Details | Examples | Details and examples

Parameters

SourceValue

Value to look up in the value lookup array.

Supported types: Numeric.

LookupArray

Specifies the array of data in which to look up the specified source value(s).

Supported types: Numeric array.

MatchType

Specifies whether the lookup must provide exact results, or be the closest value greater than or less than the value being looked up. This parameter should be set to one of the following values:

Value	Description
Equal	For each source value specified, the function finds the first item in the position lookup array that is equal to that value. If no match is found for a source value, the function returns an error. (summarize) For each source value specified, the function finds the first item in the position lookup array that is equal to that value. (more details...)
GreaterOrEqual	For each source value specified, the function finds the smallest item in the position lookup array that is greater than or equal to that value. The position lookup array should be sorted in descending order for correct results. (summarize) For each source value specified, the function finds the smallest item in the position lookup array that is greater than or equal to that value. (more details...)
LessOrEqual	For each source value specified, the function finds the largest item in the position lookup array that is less than or equal to that value. The position lookup array should be sorted in ascending order for correct results. (summarize) For each source value specified, the function finds the largest item in the position lookup array that is less than or equal to that value. (more details...)

Supported types: Constant.

Returns

Returned types: Numeric.

Examples

1.	In the following example, the value closest to 6 while respecting the match type is 10. The function returns its index, 5. LOOKUP(6, [30, 25, 20, 15, 10, 4, 0], GreaterOrEqual)

LOOKUP(SourceValue, LookupArray, MatchType, ResultLookupArray)

This variation first finds the index of the value in the position lookup array closest or equal to the value specified, depending on the match type. Then, the index refers to a value from the result lookup array, returning that value.

Details | Examples | Details and examples

Parameters

SourceValue

Value to look up in the value lookup array.

Supported types: Numeric.

LookupArray

Specifies the array of data in which to look up the specified source value(s).

Supported types: Numeric array.

MatchType

Specifies whether the lookup must provide exact results, or be the closest value greater than or less than the value being looked up. This parameter should be set to one of the following values:

Value	Description
Equal	For each source value specified, the function finds the first item in the position lookup array that is equal to that value. If no match is found for a source value, the function returns an error. (summarize) For each source value specified, the function finds the first item in the position lookup array that is equal to that value. (more details...)
GreaterOrEqual	For each source value specified, the function finds the smallest item in the position lookup array that is greater than or equal to that value. The position lookup array should be sorted in descending order for correct results. (summarize) For each source value specified, the function finds the smallest item in the position lookup array that is greater than or equal to that value. (more details...)
LessOrEqual	For each source value specified, the function finds the largest item in the position lookup array that is less than or equal to that value. The position lookup array should be sorted in ascending order for correct results. (summarize) For each source value specified, the function finds the largest item in the position lookup array that is less than or equal to that value. (more details...)

Supported types: Constant.

ResultLookupArray

Specifies the values to be looked up with the indices of data found in the lookup array. Note that this parameter must be greater than or equal in size to the lookup array.

Supported types: Numeric array; Boolean array; String array; Point array; Object array.

Returns

Returned types: Numeric; Boolean; String; Point; Object.

Examples

1.	In the following example, the value closest to 6 while respecting the match type is 5. The function uses its index, 1, to reference an element in the result lookup array, and returns 10. LOOKUP(6, [5, 10, 15, 20, 25], LessOrEqual, [10, 100, 1000, 10000, 100000])

LOOKUP(SourceArray, LookupArray, MatchType)

Returns the indices of the values in the position lookup array closest or equal to the values specified in the source array, depending on the match type. The returned array of indices is of the same length as the source values array.

Details | Examples | Details and examples

Parameters

SourceArray

Array of values to look up in the lookup array.

Supported types: Numeric array.

LookupArray

Specifies the array of data in which to look up the specified source value(s).

Supported types: Numeric array.

MatchType

Specifies whether the lookup must provide exact results, or be the closest value greater than or less than the value being looked up. This parameter should be set to one of the following values:

Value	Description
Equal	For each source value specified, the function finds the first item in the position lookup array that is equal to that value. If no match is found for a source value, the function returns an error. (summarize) For each source value specified, the function finds the first item in the position lookup array that is equal to that value. (more details...)
GreaterOrEqual	For each source value specified, the function finds the smallest item in the position lookup array that is greater than or equal to that value. The position lookup array should be sorted in descending order for correct results. (summarize) For each source value specified, the function finds the smallest item in the position lookup array that is greater than or equal to that value. (more details...)
LessOrEqual	For each source value specified, the function finds the largest item in the position lookup array that is less than or equal to that value. The position lookup array should be sorted in ascending order for correct results. (summarize) For each source value specified, the function finds the largest item in the position lookup array that is less than or equal to that value. (more details...)

Supported types: Constant.

Returns

Returned types: Numeric array.

Examples

1.	The following example searches for the values from the source array in the lookup array, then returns the indices of the closest results according to the match type. The function returns [5, 4, 3, 2, 1] LOOKUP([20, 15, 10, 5, 0], [0, 5, 10, 15, 20, 25, 30], Equal)

LOOKUP(SourceArray, LookupArray, MatchType, ResultLookupArray)

This variation first finds the indices of the values in the position lookup array closest or equal to the values specified in the source array, depending on the match type. Then, these indices refer to values from the result lookup array, returning those values. The returned array of results is of the same length as the source values array.

Details | Examples | Details and examples

Parameters

SourceArray

Array of values to look up in the lookup array.

Supported types: Numeric array.

LookupArray

Specifies the array of data in which to look up the specified source value(s).

Supported types: Numeric array.

MatchType

Specifies whether the lookup must provide exact results, or be the closest value greater than or less than the value being looked up. This parameter should be set to one of the following values:

Value	Description
Equal	For each source value specified, the function finds the first item in the position lookup array that is equal to that value. If no match is found for a source value, the function returns an error. (summarize) For each source value specified, the function finds the first item in the position lookup array that is equal to that value. (more details...)
GreaterOrEqual	For each source value specified, the function finds the smallest item in the position lookup array that is greater than or equal to that value. The position lookup array should be sorted in descending order for correct results. (summarize) For each source value specified, the function finds the smallest item in the position lookup array that is greater than or equal to that value. (more details...)
LessOrEqual	For each source value specified, the function finds the largest item in the position lookup array that is less than or equal to that value. The position lookup array should be sorted in ascending order for correct results. (summarize) For each source value specified, the function finds the largest item in the position lookup array that is less than or equal to that value. (more details...)

Supported types: Constant.

ResultLookupArray

Specifies the values to be looked up with the indices of data found in the lookup array. Note that this parameter must be greater than or equal in size to the lookup array.

Supported types: Numeric array; Boolean array; String array; Point array; Object array.

Returns

Returned types: Numeric array; Boolean array; String array; Point array; Object array.

Examples

The following example searches for the values from the source array in the lookup array, using the indices of the closest results according to the match type to reference elements within the result lookup array. The function returns [10000, 1000, 100, 10, 1].

LOOKUP([20, 15, 10, 5, 0], [0, 5, 10, 15, 20, 25], LessOrEqual, [1, 10, 100, 1000, 10000, 100000])