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
MACRO
MATCH
MERGEFIELD
NA (Not Available)
ROW
TYPE
VLOOKUP (Vertical Lookup)
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.
Plays the computer's alert sound selected in the Sound control panel.
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.
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.
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.
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.
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):
If the method is not specified, 1 is used.
•
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
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.
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.
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):
If the method is not specified, 1 is used.
•
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
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.
Use MACRO to
•
play a macro that you previously recorded
•
execute a button (the same as clicking it)
A button can play a macro, open a document, start another application, go to a specific page on the World Wide Web, or execute a script.
•
execute an AppleScript or any script that complies with the Open Scripting Architecture (OSA)
Format:
•
for buttons and macros: =MACRO(name,type)
•
for scripts: =MACRO(name,type,subroutine,...)
Arguments:
•
name: the exact name (case-sensitive) of the macro, button, or script, enclosed in quotation marks
•
type (optional): a number that indicates what to execute
•
0: play a macro (default if no type specified)
•
1: execute a button
•
2: execute a script
•
subroutine: the name of the subroutine to execute, followed by required parameters. Separate additional parameters with commas.
MACRO returns a value indicating the result:
For
MACRO returns
When
Macros and buttons
0
AppleWorks executes the macro or button
#N/A!
AppleWorks can't find a macro or button by that name
Scripts
the result of the script
AppleWorks executes the script
#N/A!
AppleWorks can't find a script by that name
#SCRIPT!
AppleWorks encounters an error executing the script
#USER!
the script returns an error value
Example: =IF(D7>65, MACRO("Pass",0)MACRO("Fail",0))
If the value in cell D7 is 65 or greater, this formula executes a macro called "Pass." If the value in cell D7 is 64 or less, the formula executes a macro called "Fail."
Tips:
•
To see the names of macros, choose Macros from the File menu, and then choose Edit Macros. Type the macro name exactly as you see it in the list. (If the Edit Macros command is dimmed, you must first record the macro you want to use.)
•
To see the names of buttons, choose Preferences from the Edit menu, then choose Button Bar. Type the button name exactly as you see it in the list.
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):
If the method is not specified, 1 is used.
•
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
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.
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.
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.
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.
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
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):
If the method is not specified, 1 is used.
•
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
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.
Related topic
Table of contents | Index