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")
    Try this example
    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)
    Try this example
    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)
    Try this example
    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})
    Try this example
    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)
    Try this example
    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)
    Try this example
    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]])
    Try this example
    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])
    Try this example
    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)
    Try this example
    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})
    Try this example
    lookup_value (any)
    The lookup value.
    lookup_array (array)
    The array or range to search.