Lookup

row_num: row number

col_num: column number

abs_num: 1 for an absolute, 2 for a row absolute and column relative, 3 for a row relative and column absolute, and 4 for a relative reference; defaults to 1

a1: if TRUE, an A1-style reference is provided, otherwise an R1C1-style reference; defaults to TRUE

text: name of the worksheet, defaults to no sheet

If row_num or col_num is less than one, ADDRESS returns #VALUE! If abs_num is greater than 4 ADDRESS returns #VALUE!

COLUMNNUMBER.

reference: range

ADDRESS, INDEX, INDIRECT, OFFSET.

v: value

TRANSPOSE.

index: positive number

value1: first value

value2: second value

CHOOSE returns its (index+1)th argument.

index is truncated to an integer. If index < 1 or the truncated index > number of values, CHOOSE returns #VALUE!

IF.

x: reference, defaults to the position of the current expression

COLUMN function returns a Nx1 array containing the sequence of integers from the first column to the last column of x.

If x is neither an array nor a reference nor a range, returns #VALUE!

COLUMNS, ROW, ROWS.

name: column name such as "IV"

If name is invalid, COLUMNNUMBER returns #VALUE!

ADDRESS.

reference: array or area

If reference is neither an array nor a reference nor a range, COLUMNS returns #VALUE!

COLUMN, ROW, ROWS.

matrix: range

vertical: if true, matrix is flipped vertically, otherwise horizontally; defaults to TRUE

TRANSPOSE.

value: search value

range: range to search

row: 1-based row offset indicating the return values

approximate: if false, an exact match of value must be found; defaults to TRUE

as_index: if true, the 0-based column offset is returned; defaults to FALSE

HLOOKUP function finds the row in range that has a first cell similar to value. If approximate is not true it finds the column with an exact equality. If approximate is true, it finds the last column with first value less than or equal to value. If as_index is true the 0-based column offset is returned.

If approximate is true, then the values must be sorted in order of ascending value. HLOOKUP returns #REF! if row falls outside range.

VLOOKUP.

link_location: string

label: string, optional

HYPERLINK function currently returns its 2nd argument, or if that is omitted the 1st argument.

array: cell or inline array

row: desired row, defaults to 1

col: desired column, defaults to 1

area: from which area to select a cell, defaults to 1

INDEX gives a reference to a cell in the given array. The cell is selected by row and col, which count the rows and columns in the array.

If the reference falls outside the range of array, INDEX returns #REF!

ref_text: textual reference

format: if true, ref_text is given in A1-style, otherwise it is given in R1C1 style; defaults to true

If ref_text is not a valid reference in the style determined by format, INDIRECT returns #REF!

AREAS, INDEX, CELL.

value: value to look up

vector1: range to search:

vector2: range of return values

If vector1 has more rows than columns, LOOKUP searches the first row of vector1, otherwise the first column. If vector2 is omitted the return value is taken from the last row or column of vector1.

If LOOKUP can't find value it uses the largest value less than value. The data must be sorted. If value is smaller than the first value it returns #N/A. If the corresponding location does not exist in vector2, it returns #N/A.

VLOOKUP, HLOOKUP.

seek: value to find

vector: n by 1 or 1 by n range to be searched

type: +1 (the default) to find the largest value ≤ seek, 0 to find the first value = seek, or -1 to find the smallest value ≥ seek

MATCH searches vector for seek and returns the 1-based index.

For type = -1 the data must be sorted in descending order; for type = +1 the data must be sorted in ascending order. If seek could not be found, #N/A is returned. If vector is neither n by 1 nor 1 by n, #N/A is returned.

LOOKUP.

range: reference or range

row: number of rows to offset range

col: number of columns to offset range

height: height of the offset range, defaults to height of range

width: width of the offset range, defaults to width of range

OFFSET returns the cell range starting at offset (row,col) from range of height height and width width.

If range is neither a reference nor a range, OFFSET returns #VALUE!

COLUMN, COLUMNS, ROWS, INDEX, INDIRECT, ADDRESS.

x: reference, defaults to the position of the current expression

ROW function returns a 1xN array containing the sequence of integers from the first row to the last row of x.

If x is neither an array nor a reference nor a range, returns #VALUE!

COLUMN, COLUMNS, ROWS.

reference: array, reference, or range

If reference is neither an array nor a reference nor a range, ROWS returns #VALUE!

COLUMN, COLUMNS, ROW.

reference: reference or literal sheet name, defaults to the current sheet

If reference is neither a reference nor a literal sheet name, SHEET returns #VALUE!

SHEETS, ROW, COLUMNNUMBER.

reference: array, reference, or range, defaults to the maximum range

If reference is neither an array nor a reference nor a range, SHEETS returns #VALUE!

COLUMNS, ROWS.

ref: list of numbers

order: 0 (descending order) or 1 (ascending order); defaults to 0

Strings, booleans, and empty cells are ignored.

ARRAY.

matrix: range

FLIP, MMULT.

value: search value

range: range to search

column: 1-based column offset indicating the return values

approximate: if false, an exact match of value must be found; defaults to TRUE

as_index: if true, the 0-based row offset is returned; defaults to FALSE

VLOOKUP function finds the row in range that has a first cell similar to value. If approximate is not true it finds the row with an exact equality. If approximate is true, it finds the last row with first value less than or equal to value. If as_index is true the 0-based row offset is returned.

If approximate is true, then the values must be sorted in order of ascending value. VLOOKUP returns #REF! if column falls outside range.

HLOOKUP.