|
![]() |
|
Index | Which expression to use. To use the first expression, enter 1, to use the second, enter 2, and so forth. If the index value is less than 1 or greater than the number of expression, the function returns the error #ARG! |
expression | The value or references to use in the evaluation. |
CHOOSE( 2, A2, B4, C9)
This function returns the value of the cell B4 since it is the second expression in the series.
Returns the column number for the given cell or formula.
Syntax
COL( {cell-reference} )
Arguments
cell-reference Cell name . If omitted the function assumes the cell for the function. Example
COL( A2 ) = 1
Column A is the 1st column in the spreadsheet.
Returns an error. Useful embedding in other functions or conditional statements. See also the NA function.
Syntax
ERROR()
Arguments
none
Example
ERROR() = #ERROR!
This function looks for a value in the top row of a table or array and returns the value from the same column for the row you specify.
Syntax
HLOOKUP( lookup-expression, search-range, row-offset, {method} )
Arguments
lookup-expression The value you are looking for. This can be a number, text string, or reference. search-range The range to search. row-offset By default, the function searches the first row of the range defined by search-range. However, use row-offset to instruct the lookup to search a different row. The first row in the range is always 1, the second is 2, and so forth. If this value is less then 1 or greater than the number of rows in the defined range, then the function returns the error #REF! method The method of searching for the value. The default is 1.
If method < 0 Assumes decreasing values. Returns the smallest value that is greater than or equal to lookup-expression value. If method is 0 Returns first exact match. If method > 0 Assumes increasing values. Returns the largest value equal to lookup-expression value. Example
HLOOKUP( 500, A2..F5, 2 )
This function searches cells A3, B3, C3, D3, E3, and F3 for the value 500. Row 3 is searched since it is the second row in the range A2 through F5
Returns the value of a cell in a range based on the row and column offsets.
Syntax
INDEX( range, row-offset, column-offset )
Arguments
range The range of cells to search. row-offset The row offset. The first row in the range is always 1, the second is 2, and so forth. If this value is less then 1 or greater than the number of rows in the defined range, then the function returns the error #REF! column-offset The column offset. The first column in the range is always 1, the second is 2, and so forth. If this value is less then 1 or greater than the number of columns in the defined range, then the function returns the error #REF! Example
INDEX( A2..D4, 2, 2 )
This function returns the value of cell B3. This cell is the intersection between the second row and second column in the range.
This function looks for a value a table or array and returns the value from the cells where a match is found..
Syntax
LOOKUP( lookup-expression, search-range, result-range, {method} )
Arguments
lookup-expression The value you are looking for. This can be a number, text string, or reference. search-range The range or ranges to search. result-range If a match is found, the function returns those values to the range specified. method The method of searching for the value. The default is 1.
If method < 0 Assumes decreasing values. Returns the smallest value that is greater than or equal to lookup-expression value. If method is 0 Returns first exact match. If method > 0 Assumes increasing values. Returns the largest value equal to lookup-expression value. Example
HLOOKUP( 500, A2..F5, A12..F15, -1 )
This function searches cells A2 through F5 for the value 500. Results are placed in cells A12 through F15.
This function looks for a value a table or array and returns the offset value for the first instance. The search-range is checked row by row from left to right, until the value is found. If the search criteria is not met, the error #N/A! is returned.
Syntax
MATCH( lookup-expression, search-range, {method} )
Arguments
lookup-expression The value you are looking for. This can be a number, text string, or reference. search-range The range or ranges to search. method The method of searching for the value. The default is 1.
If method < 0 Assumes decreasing values. Returns the smallest value that is greater than or equal to lookup-expression value. If method is 0 Returns first exact match. If method > 0 Assumes increasing values. Returns the largest value equal to lookup-expression value. Example
MATCH( 500, A2..F5, 0 )
This function searches cells A2 through F5 for the value 500. It would return 3 if 500 was in cell A4.
Returns a not available error . Useful embedding in other functions or conditional statements. See also the ERROR function.
Syntax
NA()
Arguments
none
Example
NA() = #NA!
Returns the row number for the given cell or formula.
Syntax
ROW( {cell-reference} )
Arguments
cell-reference Cell name . If omitted, the function assumes the cell for the function. Example
ROW( A2 ) = 2
Reports the format of the evaluated expression or reference. The function returns the following values:
1 Blank 2 Logical 3 Number 4 Date/Time 5 Error 6 Text.
Syntax
TYPE( expression )
Arguments
expression value or reference to evaluate. Example
TYPE( "Gobe" )= 6.
"Gobe" is a text value.
This function looks for a value in the first column of a table or array and returns the value from the same row or for the row you specify.
Syntax
VLOOKUP( lookup-expression, search-range, column-offset, {method} )
Arguments
lookup-expression The value you are looking for. This can be a number, text string, or reference. search-range The range to search. column-offset By default, the function searches the first column of the range defined by search-range. However, use column-offset to instruct the lookup to search a different column. The first column in the range is always 1, the second is 2, and so forth. If this value is less then 1 or greater than the number of columns in the defined range, then the function returns the error #REF! method he method of searching for the value. The default is 1.
If method < 0 Assumes decreasing values. Returns the smallest value that is greater than or equal to lookup-expression value. If method is 0 Returns first exact match. If method > 0 Assumes increasing values. Returns the largest value equal to lookup-expression value. Example
VLOOKUP( 500, A2..F5, 2 )
This function searches cells B2, B3, B4, and B5 for the value 500. Column B is searched since it is the second column in the range A2 through F5
Copyright © 1999, Gobe Software, Inc |