Name

lookuptable — Extract a value from a list.

Syntax

lookuptable(key, keylist, valuelist)
              

The LOOKUPTABLE function has the following argument:

key

The key to use to lookup the value to be returned.

keylist

The list of keys that are options to be looked up.

valuelist

The list of values, one matching each key, that are options that can be returned.

Description

This function extracts a value from a list by searching a list of keys to find a matching entry, and then returning a value from a corresponding position in the value list.

The keylist and valuelist arguments may be specified as either be a list data type or as a string that will be split at commas in to a list of string values.

The key lookup is done to find an exact matching value. For this reason the key value must have a data type that is identical to the values in the keylist. So if the keylist contains int values, then the key value must also be an int.

If the keylist is a string, then then the value of the key argument will automatically be converted to a string.

The number of values in the keylist must be the same as the number of values in the valuelist.

If the key does not match a value in the keylist then the key value will be returned. This will result in an error if the key data type is not the same as the value data type.

The map function is preferred over the lookupTable function due to better handling of missing values.

Examples

Formula

Description

Result

lookuptable('a','a,b,c,d','1,2,3,4')

Split the keylist and valuelist arguments into lists of strings, return the value item in the position corresponding to the keyvalue 'a'.

'1'

lookuptable('a',list('a','b','c'),list(1,2,3))

Return the value item in the position corresponding to the keyvalue 'a'.

1

lookuptable('e','a,b,c,d','1,2,3,4')

Split the keylist and valuelist arguments into lists of strings, return the key since the it is not found in the keylist.

'e'