# Lookup & Reference Functions

Data Lookup Functions. Functions to Return References to Cell Ranges.Row / Column / Area Information.
• CHOOSE

Returns an element from a list of choices based on index.

CHOOSE(2,"A","B","C")
 index (number) Which choice (of the up to 29 provided) to return. choice1 (any) A potential value to return. Required. May be a reference to a variable or an individual value.
• HLOOKUP

Horizontal lookup. Searches across the first row of a range for a key and returns the value of a specified variable in the column found.

HLOOKUP(10003, {var1}, 2, FALSE)
 search_key (string) The value to search for. For example, 42, "Cats" range (array) The range to consider for the search. The first row in the range is searched for the key specified in search_key.
• INDEX

Returns the content of an element, specified by row and column offset.

INDEX({var1}, 5, 1)
 reference (array) The array of cells to be offset into. row (number) Optional - 0 by default - The number of offset rows.
• LOOKUP

Looks through a sorted row or column for a key and returns the value of the variable in a result range located in the same position as the search row or column.

LOOKUP("foo", {var1})
 search_key (string) The value to search for in the row or column. For example, 42, "Cats". search_range (array) One method of using LOOKUP is to provide a single row or column search_range to look through for the search with a second argument result_range. The other way is to combine these two arguments into one search_result_array where the first row or column is searched and a value is returned from the last row or column in the array.
• MATCH

Returns the relative position of an item in a range that matches a specified value.

MATCH("Sunday",{var1},0)
 search_key (any) The value to search for. For example, 42, "Cats" range (array) The one-dimensional array to be searched.
• OFFSET

Returns a range reference shifted a specified number of rows and columns from a starting cell reference.

OFFSET({var1},1,1)
 cell_reference (array) The starting point from which to count the offset rows and columns. offset_rows (number) The number of rows to shift by.
• TRANSPOSE

Transposes the rows and columns of an array or range of variables.

TRANSPOSE([[1,2],[3,4],[5,6]])
 array_or_range (array) The array or range whose rows and columns will be swapped.
• UNIQUE

Returns a list of unique values in a list or range

UNIQUE([1,1,2,2])
 array (array) Range or array from which to extract unique values.
• VLOOKUP

Vertical lookup. Searches down the first column of a range for a key and returns the value of a specified variable in the row found.

VLOOKUP(10003, {var1}, 2, FALSE)
 search_key (string) The value to search for. For example, 42, "Cats" range (array) The range to consider for the search. The first column in the range is searched for the key specified in search_key.
• XMATCH

Returns the relative position of an item in an array or range of cells.

XMATCH({var1},{var2})
 lookup_value (any) The lookup value. lookup_array (array) The array or range to search.