Syntax and practical examples of support functions in the library.
Returns a cell reference given a row and column.
ADDRESS(1, 1) = "$A$1"
ADDRESS(1, 2, 2) = "B$1"
ADDRESS(3, 2, 3) = "$B3"
Returns the column number of a specified cell.
COLUMN(A1) = "A"
COLUMN("A1") = "A"
Returns the number of columns in a specified array or range.
COLUMNS({1,2,3,4,5}) = 5
All columns must be same size.
COLUMNS({1,2,3,4,5;1,2,3,4,5}) = 5
When they are not the same size then ##VALUE? is returned.
ISERROR(COLUMNS({1,2,3,4,5;1,2}))
Lookup a value in a table hortizonally on the first row and retreive a value from the matching column and specified row.
HLOOKUP(lookup_value, lookup_array, index, exactmatch)
HLOOKUP("C", {{"A","B","C"},{1,2,3}}, 2) = 3
Lookup an index in an array or reference.
INDEX(array, row_num, [column_num])
INDEX({{"A","B","C"}}, 1, 1) = "A"
INDEX({{"A","B","C"}}, 1, 2) = "B"
INDEX({{"A","B","C"}, {"D"}}, 2) = "D"
INDEX({"A","B","C";"D"}, 1, 3) = "C"
INDEX(reference, row_offset, [column_offset])
Returns a cell reference given a string.
ISCELL(INDIRECT("A1"))
LOOKUP(lookup_value, lookup_vector, lookup_array)
LOOKUP("C", {"A","B","C"}, {1,2,3}) = 3
The array form searches the array by row or column depending on the dimensions of the array.
If the array is wider then tall then the search is on the first row; the result being from the matching row and last column.
If the array is taller then wide then the search is on the first column; the result being from the last row and matching column.
LOOKUP(lookup_value, lookup_array)
Wide Array:
LOOKUP("C", {"A","B","C","D","E","F";"foo";1,2,3,4,5,6}) = 3
Tall Array:
LOOKUP("C", {"A",1;"B",2;"C",3;"D",4;"E",5;"F",6}) = 3
Returns the column matched by a lookup_value in a lookup_reference.
MATCH(lookup_value, lookup_reference, match_type)
=MATCH("b",{"a","b","c"},0) = 2
=MATCH("a",{"aa","bb","cc"},0) = NA()
=MATCH("a?",{"aa","bb","cc"},0) = 1
=MATCH("?b",{"aa","bb","cc"},0) = 2
=MATCH("b~",{"aa","b?","cc"},0) = 2
=MATCH("c*c",{"aa","b?","cfoobarc"},0) = 3
Returns a reference to a cell a given number of rows and columns from a reference.
OFFSET(ref, rows, cols, height, width)
Returns the row number of a specified cell.
ROW(A1) = 1
ROW("B10") = 10
Returns the number of rows in a specified array or range.
ROWS({1;2;3;4;5;6}) = 6
Lookup a value in a table hortizonally on the first row and retreive a value from the matching row and specified column.
VLOOKUP(lookup_value, lookup_reference, index, [exactmatch])
VLOOKUP("C", {"A",1;"B",2;"C",3}, 2) = 3