Informational Functions

Informational functions provide a variety of searching and reporting procedures.

CHOOSE

Picks out a value or expression based on the index. 

Syntax

CHOOSE( index, expression, {expression} )

Arguments

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.

Example

CHOOSE( 2, A2, B4, C9)

This function returns the value of the cell B4 since it is the second expression in the series.

COL

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.

ERROR

Returns an error. Useful embedding in other functions or conditional statements. See also the NA function.

Syntax

ERROR()

Arguments

none

Example

ERROR() = #ERROR!

HLOOKUP

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

INDEX

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. 

LOOKUP

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.

MATCH

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.

NA

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!

ROW

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

TYPE

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.

VLOOKUP

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