CHOOSE(n, list)
Select the nth value from the subsequent list.
Blank elements are included in the calculation.
COL
Returns the number of the column containing the cell in which the function is evaluated.
COL(cell_reference)
Returns the number of the column of cell_reference.
COL(A1)
returns 1.
COL(range)
Returns the number of the starting column of range.
COL(E1F10)
returns 5.
This is particularly useful if you have a name which is defined to be a range.
COL(apples)
where apples
is a name defined to be a range, returns the number of the first column of the range.
COLS
COLS with no parameters returns the number of columns in the document.
COLS(array_or_range)
Returns the number of columns in array_or_range.
COLS({1,2,3})
returns 3.
HLOOKUP(key, range, offset_rows)
This function differs from LOOKUP in three ways:
INDEX(array_or_range, col, row {, cols, rows})
Returns the element of the array_or_range indicated by col, row.
Indices start at 1.
INDEX((1,2;3,4},2,2)
returns 4.
INDEX can be made to return an array itself by including the optional rows and cols parameters which tell INDEX how large an array to return.
INDEX(A1C3,2,2,2,2)
returns an array containing {B2,C2;B3,C3}
.
Starting with 4.56, this function will now accept zero for the column and/or row number.
If the column number is zero, the whole of the specified row(s) is returned.
If the row number is zero, the whole of the specified column(s) is returned.
Starting with 4.56, this function will now accept zero for the optional column and/or row size.
If the column size is zero, the rest of the specified row(s) beginning at the given column number is returned.
If the row size is zero, the whole of the specified column(s) beginning at the given row number is returned.
LOOKUP(key, array_or_range1, array_or_range2)
Returns the value in array_or_range2 corresponding to the position that key occurs in array_or_range1.
key can be a date, a string or a number.
If it is a string, it can include the wildcards
^?
(any single character) and
^#
(any multiple characters).
If an exact match is found in array_or_range1, the function returns the value of the cell the same number of elements from the beginning of array_or_range2.
If key is not found in array_or_range1, an error message is displayed.
MATCH(key, array_or_range, lookup_type)
Looks up key in array_or_range using lookup_type and returns its cell reference.
If lookup_type is 1, finds the largest value which is less than or equal to key. Note that, in this case, array_or_range must be sorted in ascending order.
If lookup_type is 0, finds the value which exactly matches key.
If lookup_type is -1, finds the smallest value which is greater than or equal to key. Note that, in this case, array_or_range must be sorted in descending order.
MATCH(2.5,{1,2,3},1)
returns 2.
MATCH(2.5,A1A3,1)
where A1 contains 1, A2 contains 2 and A3 contains 3, returns A2.
Use the COL and ROW functions to return the offset from the start of the range.
ROW
Returns the number of the row containing the cell in which the function is evaluated.
ROW(cell_reference)
Returns the number of the row of cell_reference.
ROW(A1)
returns 1.
ROW(range)
Returns the number of the starting row of range.
ROW(E3F10)
returns 3.
ROWS
ROWS with no parameters returns the number of rows in the document.
ROWS(array_or_range)
Returns the number of rows in array_or_range.
ROWS(A1F1O)
returns 10.
VLOOKUP(key, range, offset_cols)
This function differs from LOOKUP in three ways: