Information functions

Information functions return numeric, text, or logical values. Arguments are text, numeric expressions, and valid cells.

  ALERT
BEEP
CHOOSE
COLUMN
ERROR
HLOOKUP (Horizontal Lookup)
INDEX
LOOKUP
MATCH
MERGEFIELD
NA (Not Available)
ROW
TYPE
VLOOKUP (Vertical Lookup)

ALERT

Displays a message in a dialog box, and returns a value indicating which button was clicked to close the dialog box.

The ALERT function is evaluated when
you first use it in a formula
you change one of its arguments
another function calls it (for example, when ALERT is called as the result of an IF evaluation)
you choose Calculate Now from the Calculate menu
you open the spreadsheet for the first time on a computer of a different type (for example, when you open a spreadsheet created on a Macintosh computer on a PC using Windows)

Note: The ALERT function is not available in databases.

Format: =ALERT(message,type)

Arguments:
message: the text of the message you want to display when the ALERT function is evaluated
type (optional): the buttons to display (see table). If you omit type, the message box displays only an OK button and returns the message text.

  To display   Specify   Values returned
OK, Cancel
OK highlighted
1 1=OK
0=Cancel

Yes, No
Yes highlighted
2 1=Yes
0=No

OK, Cancel
Cancel highlighted
3 1=OK
0=Cancel

Example: =IF(D7>100,ALERT("Limit exceeded!",3),D7)

If the value in cell D7 is 101 or greater, this formula displays a dialog box with the message "Limit exceeded!". The dialog box contains OK and Cancel buttons, with the OK button highlighted. Clicking OK returns 1; clicking Cancel returns 0.

If the value in cell D7 is 100 or less, this formula returns the value in cell D7.

Top

BEEP

Plays the alert sound selected in the Sound pane of System Preferences.

Format: =BEEP()

Argument: No argument

Example: =IF(A1>=10,BEEP(),"Less than 10")

If the value in cell A1 is greater than or equal to 10, the computer's alert is played. If the value in cell A1 is less than 10, the text "Less than 10" is displayed.

Top

CHOOSE

Returns the value specified by the argument INDEX from a list of values (up to 250 characters).

Format: =CHOOSE(index,value1,value2,...)

Arguments:
index: a number or numeric expression
value: a text or numeric expression

Example: =CHOOSE(3,"1st","second",7,"last") returns 7. The index is 3, and 7 is the third value listed.

Top

COLUMN

Returns the column number of the current cell or the cell referred to.

Note: The COLUMN function is not available in databases.

Format: =COLUMN(cell)

Argument:
cell (optional): a cell reference

Example: =COLUMN(B7) returns 2. The cell B7 is in the B column, which is column 2.

Top

ERROR

Returns the value ERROR!

Format: =ERROR()

Argument: No argument

Example: =IF(ISBLANK(A1),"Hi!",ERROR()) returns "Hi!" if cell A1 is blank and #ERROR! if it has data in it. =IF(ISTEXT(B6),"OK",ERROR()) returns "OK" if cell B6 contains text and #ERROR! if it contains numbers.

Top

HLOOKUP (Horizontal Lookup)

Searches for a specific value within the top row of a range of cells. You can specify whether to search for the closest match or an exact match.

If HLOOKUP finds a match that meets your criteria, it adds an offset that you specify to the row number of the cell where it found the match, and then returns the value in that cell. (For example, if it finds a match in cell B7 and you've specified an offset of 3, it returns the value in cell B10.) If HLOOKUP can't find a match, it returns an error.

Note: The HLOOKUP function is not available in databases.

Format: =HLOOKUP(lookup value,compare range,offset,method)

Arguments:
lookup value: the text or numeric expression you want to search for
compare range: a cell range of one or more rows
The first row contains the data to be searched; the remaining rows contain values you want the function to return.
If the values in the compare range are not in strict ascending or descending order, or the compare range does not contain an inclusive range from the highest to lowest possible value, the results can be unpredictable.
offset: a numeric expression that specifies how many rows down HLOOKUP most go to find the value to be returned
method (optional):
1: when the values in the first row increase from left to right, use to locate the largest value less than or equal to the lookup value
-1: when the values in the first row decrease from left to right, use to locate the smallest value greater than or equal to the lookup value
0: use to return the exact match to the lookup value
If the method is not specified, 1 is used.

Example: In a spreadsheet containing this data:

=HLOOKUP(73,A1..F3,1,1) returns C.
=HLOOKUP(73,A1..F3,1,-1) returns an error because the first row of values are in increasing order of value.
If cell A9 contains 91, =HLOOKUP(A9,A1..F3,2,1) returns Excellent.
=HLOOKUP(86,A1..E3,1,0) returns an error (#N/A!) because there is no exact match for the value 86.

Tip: The example shows the importance of having an inclusive set of values in the compare range. If you omit columns A and F, a search value of 48 or 100 returns an error.

Top

INDEX

Returns the value of a specified cell within a range of cells. You specify the location by indicating how many rows to the right and how many columns down the cell is in relation to the upper-left corner of the range.

Note: The INDEX function is not available in databases.

Format: =INDEX(range,row,column)

Arguments:
range: a cell range containing the values you want to return
row: a numeric expression that specifies the cell's row position within the range
column: a numeric expression that specifies the cell's column position within the range

Example: In a spreadsheet containing this data:

=INDEX(A1..D4,1,1) returns 2.
=INDEX(A1..D4,3,2) returns 1024.
=INDEX(A1..D4,2,3) returns 128.
=INDEX(A1..D4,1,5) returns an error (#REF!) because the range contains only four columns.

Top

LOOKUP

Searches for a specific value within a range of cells. You can specify whether to search for the closest match or an exact match.

If LOOKUP finds a match that meets your criteria, it returns the value in the corresponding cell of a second range. If LOOKUP can't find a match, it returns an error.

Note: The LOOKUP function is not available in databases.

Format: =LOOKUP(lookup value,compare range,result range,method)

Arguments:
lookup value: the text or numeric expression you want to search for
compare range: a cell range
If the values in the compare range are not in strict ascending or descending order, or the compare range does not contain an inclusive range from the highest to lowest possible value, the results can be unpredictable.
result range: a cell range
method (optional):
1: when the values in the first row increase from left to right, use to locate the largest value less than or equal to the lookup value
-1: when the values in the first row decrease from left to right, use to locate the smallest value greater than or equal to the lookup value
0: use to return the exact match to the lookup value
If the method is not specified, 1 is used.

Example: In a spreadsheet containing this data:

=LOOKUP(97,A1..G2,A3..G4,1) returns APR.
=LOOKUP(97,A1..G2,A3..G4,-1) returns an error because the compare range is in increasing order of value.
If cell A9 contains 259, =LOOKUP(A9,A1..G2,A3..G4,1) returns SEP.
=LOOKUP(364,A1..G2,A3..G4,0) returns an error because there is no exact match for 364.

Tip: The example shows the importance of having an inclusive set of values in the compare range. If you omit column G, search values of 335 and higher return an error.

Top

MATCH

Searches for a specific value within a range of cells, from left to right and top to bottom. You can specify whether MATCH searches for the closest match or an exact match.

If MATCH finds a value that meets your criteria, it returns the location of that value within the range. The location represents the number of cells, counting left to right and top to bottom, from the upper-left cell in the range.

Note: The MATCH function is not available in databases.

Format: =MATCH(lookup value,compare range,method)

Arguments:
lookup value: the text or numeric expression you want to search for
compare range: a cell range
If the values in the compare range are not in strict ascending or descending order, or the compare range does not contain an inclusive range from the highest to lowest possible value, the results can be unpredictable.
method (optional):
1: when the values in the first row increase from left to right, use to locate the largest value less than or equal to the lookup value
-1: when the values in the first row decrease from left to right, use to locate the smallest value greater than or equal to the lookup value
0: use to return the exact match to the lookup value
If the method is not specified, 1 is used.

Example: In a spreadsheet containing this data:

=MATCH(2,A1..D4,1) returns 1.
If cell A9 contains 64, =MATCH(A9,A1..D4,1) returns 6 because the nearest match (64) is the sixth cell in the range A1 to D4, counting left to right from A1 to D4, and then dropping down to row 2 and counting A2 and B2.
=MATCH(2,A1..D4,-1) returns an error because the compare range is in increasing order of value.

Top

MERGEFIELD

Merges data from an AppleWorks database into an AppleWorks spreadsheet. MERGEFIELD is set to return the name of the database field from which data is merged.

To save or print the spreadsheet document with merged data, choose Mail Merge from the File menu, open the database containing the data, click Merge in the Mail Merge window, and choose a destination for your merged documents.

Note: The MERGEFIELD function is not available in databases.

Format: =MERGEFIELD(text)

Argument:
text: a string, cell reference, or expression that produces a database field name

Example:
MERGEFIELD("Name") merges data from the Name field into the cell.
=IF((MOD(A1,4)=0),MERGEFIELD("Quarterly Total"),MERGEFIELD("Monthly Total")) merges data from one of two summary fields, depending on the value stored in a cell:
If the value in cell A1 (which contains the number of the current month) is evenly divisible by 4, the merge document contains data from the Quarterly Total summary field.
Otherwise, the merge document contains data from the Monthly Total summary field.

Top

NA (Not Available)

Returns the value N/A!

Note: The NA function is not available in databases.

Format: =NA()

Argument: No argument

Example: =IF(ISBLANK(A1),"Hi!",NA()) returns "Hi!" if cell A1 is empty and #N/A! if it has data in it. =IF(ISNUMBER(B6),"OK",NA()) returns "OK" if cell B6 contains numbers and #NA! if it contains text.

Top

ROW

Returns the row number of the current cell or the cell referred to.

Note: The ROW function is not available in databases.

Format: =ROW(cell)

Argument:
cell (optional): a cell reference

Example: =ROW(B7) returns 7 because the cell B7 is in the seventh row.

Top

TYPE

Returns a number that represents one of four types of data that can be used in the argument value: blank, logical, number, and text.

Format: =TYPE(value)

Argument:
value: any expression

Example:

  This expression   Returns   For this type of data
A5 1 Blank (if cell A5 is empty)

TRUE 2 Logical

1.5 3 Number

"hello" 4 Text

PI() 3 Number

Top

VLOOKUP (Vertical Lookup)

Searches for a specific value within the left column of a range of cells. You can specify whether to search for the closest match or an exact match.

If VLOOKUP finds a match that meets your criteria, it adds an offset that you specify to the column letter of the cell where it found the match, and then returns the value in that cell. (For example, if it finds a match in cell B7 and you've specified an offset of 3, it returns the value in cell E7.) If VLOOKUP can't find a match, it returns an error.

Note: The VLOOKUP function is not available in databases.

Format: =VLOOKUP(lookup value,compare range,offset,method)

Arguments:
lookup value: the text or numeric expression you want to search for
compare range: a cell range of one or more columns
The first (leftmost) column contains the data to be searched; the remaining columns contain values you want the function to return.
If the values in the compare range are not in strict ascending or descending order, or the compare range does not contain an inclusive range from the highest to lowest possible value, the results can be unpredictable.
offset: a numeric expression that specifies how many rows to the right VLOOKUP must go to find the value to be returned
method (optional):
1: when the values in the first column are in ascending order from top to bottom, use to locate the largest value less than or equal to the lookup value
-1: when the values in the first column are in descending order from top to bottom, use to locate the smallest value greater than or equal to the lookup value
0: use to return the exact match to the lookup value
If the method is not specified, 1 is used.

Example: In a spreadsheet containing this data:

=VLOOKUP(73,A1..C6,1,1) returns C.
=VLOOKUP(73,A1..C6,1,-1) returns an error because the values in the left column are in increasing order of value.
If cell A9 contains 91, =VLOOKUP(A9,A1..C6,2,1) returns Excellent.
=VLOOKUP(86,A1..C6,1,0) returns an error because there is no exact match for the value 86.

Tip: The example shows the importance of having an inclusive set of values in the compare range. If you omit rows 1 and 6, a search value of 48 or 100 returns an error.

Top

Related topic

  Alphabetical list of functions

 
Table of contents Index