Investment Studio > Expressions > Functions > Reference > LOOKUP

Array form: lookup(lookup_value, array[*][*] table)

Locates lookup_value in the first column of table (if table is square or has more rows than columns) or in the first row of table (if table has more columns than rows), returns the corresponding entry in the last column or row.
Vector form: lookup(lookup_value, array values, array results)

Locates lookup_value in the values array, returns the entry in the corresponding position (same order of appearance) of the results array.

The number of elements in the results array must be greater than or equal to the number of elements in the values array.

In both array and vector form, lookup_value and array values can be of any type. Matching is approximate (the largest value <= lookup_value is used) and follows the same rules as the comparison operators.

It's recommended that you use hlookup or vlookup instead of the array form of the lookup function.

The behaviour of hlookup, vlookup and the array form of lookup is quite similar. There are three important differences: when you use lookup,

  1. the search direction is determined by the dimensions of the argument array (the search is conducted along the longer edge, as noted above);
  2. the result is always fetched from the last column or row (the edge opposite to the searched column or row); and
  3. matching is always approximate (the largest value <= lookup_value is used).

With hlookup and vlookup, the search direction is determined by the choice of function, the result can be taken from any column or row and matching can be either approximate or exact.

Array form examples

=lookup(2, {{1, 2, 3}, {4, 5, 6}})

returns 5 (there are more columns than rows, so search is along the top row; 2 is an exact match, and the corresponding value in the opposite row is returned)

=lookup("hello", {{1, "hi", "goodbye"}, {10, 20, 30}})

returns 30 (again searching along top row, "goodbye" is the closest match <= "hello"; 30 is the corresponding value in the opposite row)

=lookup("geek", {{1, "hi"}, {"faulkner", 10}, {20, 30}})

returns 10 (searching along the left column, "faulkner" is the closest match <= "geek"; 10 is the corresponding value in the opposite column)

=lookup(0, {{1, "hi"}, {"goodbye", 10}, {20, 30}})

returns #N/A (no value <= 0 in left column).

Vector form examples

=lookup("sisko", {"archer", "kirk", "picard", "sisko", "janeway", "braxton"}, {1, 2, 3, 4, 5, 6})

returns 4 (exact match in fourth position of row value vector, returns value of fourth element in result vector)

=lookup("sisko", {{"archer", "kirk"}, {"picard", "sisko"}, {"janeway", "braxton"}}, {1, 2, 3, 4, 5, 6})

also returns 4: the value array and the result vector have different dimensions but the same number of elements, and corresponding positions are determined by order of appearance

=lookup("sisko", {{"archer", "kirk"}, {"picard", "sisko"}, {"janeway"}}, {1, 2, 3, 4, 5})

is an error (#VALUE!): although only 5 elements are specified, the dimensions of the value array are 2 x 3, so the result vector is too short

=lookup("rho", {{"archer"}, {"kirk"}, {"picard"}, {"sisko"}, {"janeway"}}, {1, 2, 3, 4, 5})

returns 3 ("picard" is the largest element <= "rho" in the value array; 3 is the value in the corresponding position of the result array).

See also hlookup, index, match, vlookup.