Lookup

  • ADDRESS cell address as text
  • AREAS number of areas in reference
  • ARRAY vertical array of the arguments
  • CHOOSE the (index+1)th argument
  • COLUMN vector of column numbers
  • COLUMNNUMBER column number for the given column called name
  • COLUMNS number of columns in reference
  • FLIP matrix flipped
  • HLOOKUP search the first row of range for value
  • HYPERLINK second or first arguments
  • INDEX reference to a cell in the given array
  • INDIRECT contents of the cell pointed to by the ref_text string
  • LOOKUP contents of vector2 at the corresponding location to value in vector1
  • MATCH the index of seek in vector
  • OFFSET an offset cell range
  • ROW vector of row numbers
  • ROWS number of rows in reference
  • SHEET sheet number of reference
  • SHEETS number of sheets in reference
  • SORT sorted list of numbers as vertical array
  • TRANSPOSE the transpose of matrix
  • VLOOKUP search the first column of range for value

ADDRESS

ADDRESS cell address as text

Synopsis

ADDRESS(row_num,col_num,abs_num,a1,text)

Arguments

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

Note

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

See also

COLUMNNUMBER.

AREAS

AREAS number of areas in reference

Synopsis

AREAS(reference,…)

Arguments

reference: range

See also

ADDRESS, INDEX, INDIRECT, OFFSET.

ARRAY

ARRAY vertical array of the arguments

Synopsis

ARRAY(v,…)

Arguments

v: value

See also

TRANSPOSE.

CHOOSE

CHOOSE the (index+1)th argument

Synopsis

CHOOSE(index,value1,value2,…)

Arguments

index: positive number

value1: first value

value2: second value

Description

CHOOSE returns its (index+1)th argument.

Note

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

See also

IF.

COLUMN

COLUMN vector of column numbers

Synopsis

COLUMN(x)

Arguments

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

Description

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

Note

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

See also

COLUMNS, ROW, ROWS.

COLUMNNUMBER

COLUMNNUMBER column number for the given column called name

Synopsis

COLUMNNUMBER(name)

Arguments

name: column name such as "IV"

Note

If name is invalid, COLUMNNUMBER returns #VALUE!

See also

ADDRESS.

COLUMNS

COLUMNS number of columns in reference

Synopsis

COLUMNS(reference)

Arguments

reference: array or area

Note

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

See also

COLUMN, ROW, ROWS.

FLIP

FLIP matrix flipped

Synopsis

FLIP(matrix,vertical)

Arguments

matrix: range

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

See also

TRANSPOSE.

HLOOKUP

HLOOKUP search the first row of range for value

Synopsis

HLOOKUP(value,range,row,approximate,as_index)

Arguments

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

Description

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.

Note

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

See also

VLOOKUP.

HYPERLINK

HYPERLINK second or first arguments

Synopsis

HYPERLINK(link_location,label)

Arguments

link_location: string

label: string, optional

Description

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

INDEX

INDEX reference to a cell in the given array

Synopsis

INDEX(array,row,col,area)

Arguments

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

Description

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.

Note

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

INDIRECT

INDIRECT contents of the cell pointed to by the ref_text string

Synopsis

INDIRECT(ref_text,format)

Arguments

ref_text: textual reference

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

Note

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

See also

AREAS, INDEX, CELL.

LOOKUP

LOOKUP contents of vector2 at the corresponding location to value in vector1

Synopsis

LOOKUP(value,vector1,vector2)

Arguments

value: value to look up

vector1: range to search:

vector2: range of return values

Description

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.

Note

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.

See also

VLOOKUP, HLOOKUP.

MATCH

MATCH the index of seek in vector

Synopsis

MATCH(seek,vector,type)

Arguments

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

Description

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

Note

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.

See also

LOOKUP.

OFFSET

OFFSET an offset cell range

Synopsis

OFFSET(range,row,col,height,width)

Arguments

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

Description

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

Note

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

ROW

ROW vector of row numbers

Synopsis

ROW(x)

Arguments

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

Description

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

Note

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

See also

COLUMN, COLUMNS, ROWS.

ROWS

ROWS number of rows in reference

Synopsis

ROWS(reference)

Arguments

reference: array, reference, or range

Note

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

See also

COLUMN, COLUMNS, ROW.

SHEET

SHEET sheet number of reference

Synopsis

SHEET(reference)

Arguments

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

Note

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

See also

SHEETS, ROW, COLUMNNUMBER.

SHEETS

SHEETS number of sheets in reference

Synopsis

SHEETS(reference)

Arguments

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

Note

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

See also

COLUMNS, ROWS.

SORT

SORT sorted list of numbers as vertical array

Synopsis

SORT(ref,order)

Arguments

ref: list of numbers

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

Note

Strings, booleans, and empty cells are ignored.

See also

ARRAY.

TRANSPOSE

TRANSPOSE the transpose of matrix

Synopsis

TRANSPOSE(matrix)

Arguments

matrix: range

See also

FLIP, MMULT.

VLOOKUP

VLOOKUP search the first column of range for value

Synopsis

VLOOKUP(value,range,column,approximate,as_index)

Arguments

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

Description

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.

Note

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

See also

HLOOKUP.