SpreadCE Help

Lookup/reference functions

[ ] indicates optional parameters

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
AREAS(reference)
CHOOSE(index_num, value1, [value2, ...])
COLUMN([reference])
COLUMNS(range)
HLOOKUP(lookup_value, table_range, row_index_num, [range_lookup])
INDEX(reference, [row_num], [col_num], [area_num])
INDIRECT(ref_text, [a1])
LOOKUP(lookup_value, lookup_range, [result_range])
MATCH(lookup_value, lookup_range, [match_type])
OFFSET(reference, rows, cols, [height], [width])
ROW([reference])
ROWS(range)
TRANSPOSE(array)
VLOOKUP(lookup_value, table_range, col_index_num, [range_lookup])

Lookup/reference functions

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

Returns a string containing the specified cell address.

row_num | The row number of the cell.
column_num | The column number of the cell.
abs_num | A number representing whether the row or column are to be absolute or relative. The possible values are:

1 | Both absolute
2 | Absolute row, relative column
3 | Relative row, absolute column
4 | Both relative

| If this parameter is omitted it defaults to 1.
a1 | Specifies the style of the reference. The possible values are:

FALSE | R1C1 style
TRUE | A1 style

| If this parameter is omitted it defaults to TRUE.
sheet_text | Optional sheet name with which to prefix the reference.

AREAS(reference)

Returns the number of areas contained in the reference.

reference | The reference whose areas you want to count.

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

Returns one of several values depending on the index.

index_num | The index of the value to be returned, should be in the range 1 to 29.
value1, ... | Up to 29 values, one of which will be chosen to be the result.

COLUMN([reference])

Returns the column number of the reference.

reference | The reference whose column number you want. If this parameter is omitted it defaults to the cell containing the function.

COLUMNS(range)

Returns the number of columns in the reference.

range | The reference whose columns you want to count.

HLOOKUP(lookup_value, table_range, row_index_num, [range_lookup])

Returns a value from a horizontal table, found by searching for the lookup value in the top row of the table and then returning a value from the same or a different row in the table.

lookup_value | The value to be found in the table.
table_range | A reference containing the table cells.
row_index_num | The offset of the value to be returned, where 1 is the top row of the table.
range_lookup | Whether to find an approximate or exact match. The possible values are:

FALSE | An exact match is required
TRUE | If an exact match is not found, use the closest value less than the lookup value

| If this parameter is omitted it defaults to TRUE.

INDEX(reference, [row_num], [col_num], [area_num])

Returns a subset of an array or reference.

reference | The array or reference of which you want the subset.
row_num | The number of the row to return. If this parameter is omitted all rows will be returned.
col_num | The number of the column to return. If this parameter is omitted all columns will be returned.
area_num | The number of the area to return when the reference contains more than one area. If this parameter is omitted it defaults to 1.

INDIRECT(ref_text, [a1])

Returns a reference from the specified text.

ref_text | A text expression that evaluates to the name of a cell or range of cells.
a1 | Specifies the style of the reference. The possible values are:

FALSE | R1C1 style
TRUE | A1 style

| If this parameter is omitted it defaults to TRUE.

LOOKUP(lookup_value, lookup_range, [result_range])

Returns a value from a horizontal or vertical table, found by searching for the lookup value in the top row (for a horizontal table) or left column (for a vertical or square table) of the table and then returning a value from the corresponding position in the result range (if specified) or from the bottom row (for a horizontal table) or right column (for a vertical or square table) of the table.

lookup_value | The value to be found in the table.
table_range | A reference containing the table cells.
result_range | The range of cells from which to return a result. If this parameter is not specified the result will be returned from the opposite row or column of the table range.

MATCH(lookup_value, lookup_range, [match_type])

Returns a number representing the position of a value in a table.

lookup_value | The value to be found in the table.
lookup_range | A reference containing the table cells.
match_type | Whether to find an approximate or exact match. The possible values are:

1 | If an exact match is not found, use the closest value less than the lookup value (the table values should be in ascending order)
0 | An exact match is required
-1 | If an exact match is not found, use the closest value greater than the lookup value (the table values should be in descending order)

| If this parameter is omitted it defaults to 1.

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

Returns a new reference based on the specified reference.

reference | The reference to be used as a starting point.
rows | The number of rows to move the reference up (negative) or down (positive).
cols | The number of columns to move the reference left (negative) or right (positive).
height | The height of the new reference. If this parameter is omitted it defaults to the height of the old reference.
width | The width of the new reference. If this parameter is omitted it defaults to the width of the old reference.

ROW([reference])

Returns the row number of the reference.

reference | The reference whose row number you want. If this parameter is omitted it defaults to the cell containing the function.

ROWS(range)

Returns the number of rows in the reference.

range | The reference whose rows you want to count.

TRANSPOSE(array)

Returns the transposition of the specified array or reference.

array | The array or reference whose values you want to transpose.

VLOOKUP(lookup_value, table_range, col_index_num, [range_lookup])

Returns a value from a vertical table, found by searching for the lookup value in the left column of the table and then returning a value from the same or a different column in the table.

lookup_value | The value to be found in the table.
table_range | A reference containing the table cells.
col_index_num | The offset of the value to be returned, where 1 is the left column of the table.
range_lookup | Whether to find an approximate or exact match. The possible values are:

FALSE | An exact match is required
TRUE | If an exact match is not found, use the closest value less than the lookup value

| If this parameter is omitted it defaults to TRUE.