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_numThe row number of the cell.
column_numThe column number of the cell.
abs_numA number representing whether the row or column are to be absolute or relative. The possible values are:

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

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

FALSER1C1 style
TRUEA1 style

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



AREAS(reference)
Returns the number of areas contained in the reference.

referenceThe reference whose areas you want to count.



CHOOSE(index_num, value1, [value2, ...])
Returns one of several values depending on the index.

index_numThe 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.

referenceThe 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.

rangeThe 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_valueThe value to be found in the table.
table_rangeA reference containing the table cells.
row_index_numThe offset of the value to be returned, where 1 is the top row of the table.
range_lookupWhether to find an approximate or exact match. The possible values are:

FALSEAn exact match is required
TRUEIf 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.

referenceThe array or reference of which you want the subset.
row_numThe number of the row to return. If this parameter is omitted all rows will be returned.
col_numThe number of the column to return. If this parameter is omitted all columns will be returned.
area_numThe 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_textA text expression that evaluates to the name of a cell or range of cells.
a1Specifies the style of the reference. The possible values are:

FALSER1C1 style
TRUEA1 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_valueThe value to be found in the table.
table_rangeA reference containing the table cells.
result_rangeThe 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_valueThe value to be found in the table.
lookup_rangeA reference containing the table cells.
match_typeWhether to find an approximate or exact match. The possible values are:

1If an exact match is not found, use the closest value less than the lookup value (the table values should be in ascending order)
0An exact match is required
-1If 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.

referenceThe reference to be used as a starting point.
rowsThe number of rows to move the reference up (negative) or down (positive).
colsThe number of columns to move the reference left (negative) or right (positive).
heightThe height of the new reference. If this parameter is omitted it defaults to the height of the old reference.
widthThe 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.

referenceThe 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.

rangeThe reference whose rows you want to count.



TRANSPOSE(array)
Returns the transposition of the specified array or reference.

arrayThe 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_valueThe value to be found in the table.
table_rangeA reference containing the table cells.
col_index_numThe offset of the value to be returned, where 1 is the left column of the table.
range_lookupWhether to find an approximate or exact match. The possible values are:

FALSEAn exact match is required
TRUEIf an exact match is not found, use the closest value less than the lookup value

If this parameter is omitted it defaults to TRUE.