Lookup and Reference Functions
- 3 minutes to read
This document briefly describes lookup and reference functions implemented in the ASPxSpreadsheet.
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
- Returns a text reference for a specified row and column number.
AREAS(reference)
- Returns the number of areas in a range.
CHOOSE(index_num, value1, [value2], ...)
- Selects a value from a list based on its index number.
COLUMN([reference])
- Returns the first column number within the cell reference or the number of the current column if no reference is supplied.
COLUMNS(array)
- Returns the number of columns in an array or reference.
FORMULATEXT(reference)
- Returns what is displayed in the formula bar if you select the referenced cell.
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- Looks up a value in the first table row, and returns a value in the same column from another row.
HYPERLINK(link_location,friendly_name)
- Creates a hyperlink.
INDEX(array, row_num, [column_num])
- Returns the value of an element in a table or an array, selected by the row and column number indexes.
INDIRECT(ref_text, [a1])
- Returns the reference specified by a text string.
LOOKUP(lookup_value, lookup_vector, result_vector )
- vector formReturns a value from a cell in a position found by lookup in a search table.
lookup_value
is the value to look up in thelookup_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 aslookup_vector
. The function returns the value inresult_vector
at the position where the match is found inlookup_vector
.LOOKUP(lookup_value, array)
-array formReturns a value from a cell in a position found by lookup in a search table.
lookup_value
is the value that you wish to look up in the specified array andarray
is a two-dimensional array of data. The first column (or row) of an array will be used to search for thelookup_value
, and the value in the corresponding last column (or row) will be returned.MATCH(lookup_value, lookup_array, [match_type])
- Searches for a specified item in a range of cells, and returns the relative position of that item in the range.
OFFSET(reference, rows, cols, [height], [width])
- Returns a reference to a range that is located a specified number of rows and columns away from a cell or range of cells.
ROW([reference])
- Returns the first row number within the cell reference or the number of the current row if no reference is supplied.
ROWS(array)
- Returns the number of rows in an array or reference.
TRANSPOSE(array)
- Transforms a horizontal range of cells into a vertical range, and vice versa.
VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )
- Looks up a value in the first column of a table, and returns a value in the same row from another column.