CHOOSE(n,list)
Select the nth value from the subsequent list.
Blank elements are included in the calculation.
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.
VLOOKUP(key,range,offset)
This function differs from LOOKUP in three ways: