Gobe Productive User's Guide - Informational Functions

- Working in the spreadsheet environment
- Formatting cells
- Row & column sizes
- Number formats
- Insert, Delete, Move & Go to Cells
- Locking cells
- Filling cells
- Sorting cells
- Calculating cells & Dependent cells
- Formulas & functions
- Function reference
- Creating a chart
- Working with charts
- Styles
- Named selections
- Display options
- Spell checking
- Writing tools
- Views & layers
- Working with spreadsheet frames

# Informational Functions

CHOOSE | HLOOKUP | MATCH | TYPE
COL | INDEX | NA | VLOOKUP
ERROR | LOOKUP | ROW |

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