|
|
Returns a text reference to a worksheet cell.
|
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
|
AREAS
|
|
|
|
Returns the number of areas in a reference. An area is a contiguous cell range or a single cell.
|
AREAS(reference)
|
CHOOSE
|
|
|
|
Selects a value from a list based on its index number.
|
CHOOSE(index_num, value1, [value2], …)
|
COLUMN
|
|
|
|
Returns the column number of a given cell reference or the number of the current column if no reference is supplied.
|
COLUMN([reference])
|
COLUMNS
|
|
|
|
Returns the number of columns in an array or reference.
|
COLUMNS(array)
|
FORMULATEXT
|
|
|
|
Returns a formula as a string.
|
FORMULATEXT(reference)
|
GETPIVOTDATA
|
|
|
|
Returns data stored in a PivotTable report.
|
GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], …)
|
HLOOKUP
|
|
|
|
Searches for a value in the first table row, and returns a value in the same column from the specified row.
|
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
|
HYPERLINK
|
|
|
|
Creates a hyperlink.
|
HYPERLINK(link_location,friendly_name)
|
INDEX
|
|
|
|
Uses the row and column indexes to select a value from a reference or array.
|
INDEX(array, row_num, [column_num])
INDEX(reference, row_num, [column_num], [area_num])
|
INDIRECT
|
|
|
|
Returns a reference specified by a text value.
|
INDIRECT(ref_text, [a1])
|
LOOKUP
|
|
|
|
Looks in a one-row or one-column range for a value and returns a value from the same position in a second one-row or one-column range.
|
Vector form: LOOKUP(lookup_value, lookup_vector, [result_vector])
‘lookup_value’ is the value to look up in the ‘lookup_vector’ single column (single row) range, ‘lookup_vector’ is a list of data (single column or row range) used to search for the lookup_value; ‘result_vector’ is a range of the same size as ‘lookup_vector’. The function returns the value in ‘result_vector’ at the position where the match is found in ‘lookup_vector’.
Array form: LOOKUP(lookup_value, array)
‘lookup_value’ is the value that you wish to look up in the specified array and ‘array’ is a two-dimensional array of data. The first column (or row) of an array will be used to search for the ‘lookup_value’, and the value in the corresponding last column (or row) will be returned.
|
MATCH
|
|
|
|
Searches for a specified item in a cell range and returns the relative position of that item in the range.
|
MATCH(lookup_value, lookup_array, [match_type])
|
OFFSET
|
|
|
|
Returns a reference to a range that is a specified number of rows and columns from a cell or cell range.
|
OFFSET(reference, rows, cols, [height], [width])
|
ROW
|
|
|
|
Returns the row number of a given cell reference or the number of the current row if no reference is supplied.
|
ROW([reference])
|
ROWS
|
|
|
|
Returns the number of rows in a reference or array.
|
ROWS(array)
|
SINGLE
|
|
|
|
Reduces many values to a single value. This function works as follows:
• If the ‘value’ parameter is a single item, the function returns this item.
• If the ‘value’ is a range, the function returns the value from the cell in the same row or column as the formula.
• If the ‘value’ is an array, the top-left value is returned.
The SINGLE function was originally introduced in Excel with dynamic arrays
|
, but later was replaced with the @ operator
|
Returns a vertical range of cells as a horizontal range, or vice versa.
|
TRANSPOSE(array)
|
VLOOKUP
|
|
|
|
Looks up a value in the first column of a table, and returns a value in the same row from a column you specify.
|
VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )
|
|