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 slot in which the function is evaluated.
COL(slot_ref)
Returns the number of the column of slot_ref.
For example,
COL(A1)
returns 1.
COL(range)
Returns the number of the starting column of range.
For example,
COL(E1F10)
returns 5.
This is particularly useful if you have a name which is defined to be a range.
For example, typing:
COL(apples)
where apples
is a name defined to be a range, returns the
number of the first column of the range.
COLS(array)
Returns the number of columns in array which may be a range or an array.
For example,
COLS({1,2,3})
returns 3.
COLS with no parameters returns the number of columns in the file.
HLOOKUP(key, range, offset)
This function differs from LOOKUP in three ways:
INDEX(array, col, row, cols, rows)
Returns the element of the array indicated by col, row.
array can be a range. Indices start at 1.
For example,
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.
For example,
INDEX(A1C3,2,2,2,2)
returns an array containing
{B2,C2;B3,C3}
.
LOOKUP(key, array1, array2)
Returns the value in array1 corresponding to the position that key occurs in array2.
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 array1, the function returns the value of the slot the same number of elements from the beginning of array2.
If key is not found in array1, an error message is displayed.
MATCH(key, array, lookup_type)
Looks up key in array using lookup_type and returns its slot reference.
If lookup_type is 1, finds the largest value which is less than or equal to key. Note that, in this case, array 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 must be sorted in descending order.
Examples are:
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 slot in which the function is evaluated.
ROW(slot_ref)
Returns the number of the row of slot_ref.
For example,
ROW(A1)
returns 1.
ROW(range)
Returns the number of the starting row of range.
For example,
ROW(E3F10)
returns 3.
ROWS(range)
Returns the number of rows in range. which may be a range or an array.
For example,
ROWS (A1F1O)
returns 10.
ROWS with no parameters returns the number of rows in the file.
VLOOKUP(key, range, offset)
This function differs from LOOKUP in three ways: