Lookup and reference functions

ADDRESS

Returns the cell address based on the specified row and column numbers

ADDRESS(row_num, column_num, [reference_type], [reference_style], [sheet_name])

VLOOKUP

Searches for a value in the first column of a range and returns the corresponding value from the same row and a specified column

VLOOKUP(lookup_value, range, column_num, [lookup_method])

CHOOSE

Returns a value from the list based on a specified index

CHOOSE(index, value1, [value2,...])

HYPERLINK

Creates a hyperlink in a cell

HYPERLINK(url, [link_label])

HLOOKUP

Searches for a value in the first row of a range and returns the corresponding value from the same column and a specified row

HLOOKUP(lookup_value, range, row_num, [lookup_method])

INDIRECT

Returns the contents of the cell specified by the reference

INDIRECT(link_label, [reference_style])

INDEX

Returns the contents of a cell from an area based on row and column numbers

INDEX(area, row_num, [column_num], [range_num])

AREAS

Returns the number of areas in a reference

AREAS(reference)

MATCH

Searches for a value in a one-dimensional range and returns its position in the row or column

MATCH(lookup_value, range, [lookup_method])

GETPIVOTDATA

Returns visible data from a pivot table

GETPIVOTDATA(value_field, pivot_table, [field1, item1, ...])

LOOKUP

Vector form: Looks for a value in the search range and returns its corresponding value from the result range. If the result range is omitted, the function returns the lookup value itself or the closest lesser value in the search range

Vector form: LOOKUP(lookup_value, search_range, [result_range])

Array form: Looks for a value in the first row or column of an array and returns a corresponding value from the last row or column

Array form: LOOKUP(lookup_value, array)

OFFSET

Returns a reference to a cell or range offset from the original cell or range by a specified number of rows and columns

OFFSET(reference, rows, columns, [height], [width])

COLUMN

Returns the column number for a given cell or range reference

COLUMN([reference])

ROW

Returns the row number for a given cell or range reference

ROW([reference])

TRANSPOSE

Transforms a vertical cell range into a horizontal one, or vice versa

TRANSPOSE(array)

COLUMNS

Returns the number of columns in a specified array or range

COLUMNS(array)

ROWS

Returns the number of rows in a specified array or range

ROWS(array)