Lookup and Reference Functions
- 3 minutes to read
This document briefly describes lookup and reference functions implemented in the XtraSpreadsheet.
Name | Description | Syntax |
|---|---|---|
ADDRESS | Returns a text reference for a specified row and column number. | ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) |
AREAS | Returns the number of areas in a range. | AREAS(reference) |
CHOOSE | Selects a value from a list based on its index number. | CHOOSE(index_num, value1, [value2], …) |
COLUMN | Returns the first column number within the 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 what is displayed in the formula bar if you select the referenced cell. | FORMULATEXT(reference) |
HLOOKUP | Looks up a value in the first table row, and returns a value in the same column from another row. | HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) |
HYPERLINK | Creates a hyperlink. | HYPERLINK(link_location,friendly_name) |
INDEX | Returns the value of an element in a table or an array, selected by the row and column number indexes. | INDEX(array, row_num, [column_num]) |
INDIRECT | Returns the reference specified by a text string. | INDIRECT(ref_text, [a1]) |
LOOKUP | Returns a value from a cell in a position found by lookup in a search table. | 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 range of cells, 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 located a specified number of rows and columns away from a cell or range of cells. | OFFSET(reference, rows, cols, [height], [width]) |
ROW | Returns the first row number within the cell reference or the number of the current row if no reference is supplied. | ROW([reference]) |
ROWS | Returns the number of rows in an array or reference. | ROWS(array) |
TRANSPOSE | Transforms a horizontal range of cells into a vertical range, and 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 another column. | VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] ) |