Programming Chapter 5

Reference and Lookup Functions (pg.291)

A category of functions that can look up or reference data stored in a table.

LOOKUP Function (pg.312)

The function that looks up the greatest value that does not exceed a specified value anywhere in a table or range, whether it is organized in a vertical or horizontal orientation. -Must be in ascending order. LOOKUP( lookup_value, lookup_vector, result_vector)

IFERROR (pg.332)

You can specify your own text (or even blank cell) when an error is encountered rather than the standard Excel message. IFERROR(value,value_if_error) -It combines aspects of the IF function and the ISERROR function. -It's less specific than the ISBLANK function, which only tests for a blank cell as opposed to an error message resulting from the use of a blank cell in your formula (#N/A, #REF!, #DIV/0!,etc.)

IS Functions (pg.331)

A category of functions (there are 9 IS Functions), including ISBLANK and ISNUMBER, that test a value or cell reference, then return a TRUE or FALSE value depending on the results. -Are often used in formulas to test the outcome of a calculation. -If you, combine them with the IF function, they help you locate data-entry errors.

Lookup Table (pg.291)

A data list that categorizes values you want to retrieve.

Two-DImensional Table (pg. 316)

A lookup table in which data is stored at the intersection of a column and row.

Horizontal Lookup Table (pg.303)

A lookup table in which data is stored in row instead of columns.

Vertical Lookup Table (pg.294)

A lookup table in which the data to be searched is organized in columns, the most effective and flexible way to retrieve data is to use the VLOOKUP function. (The V in VLOOKUP stands for vertical).

ISBLANK Function (pg.329)

Checks whether a specified value refers to an empty cell. ISBLANK(value) if it's blank---> TRUE if it's not blank---> FALSE

Key Data (pg.294)

The data you want to look up when using a lookup function.

CHOOSE Function (pg.336)

The function tha returns a value or a range for up to 29 different values. CHOOSE(index_num, value1, value2,...)

VLOOKUP Function (pg.293)

The function that looks up a value stored in the first column of a vertical lookup table and retrieves data stored in the same row of a subsequent column. VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) Range_Lookup: With a TRUE type---> finds the greatest value that does not exceed the lookup_value. With a FALSE type---> looks only for an exact match of the lookup_value.

HLOOKUP Formula (pg.303)

The function that looks up a value stored in the first row of a horizontal lookup table and retrieves data stored in the same column of a subsequent row. HLOOKUP(lookup_value,table_array, row_index_num, range_lookup) Range_Lookup: With a TRUE type---> finds the greatest value that does not exceed the lookup_value. With a FALSE type---> looks only for an exact match of the lookup_value.

INDEX Function (pg.315-317)

The function that retrieves data from multidimensional tables. INDEX(reference, row_num, column_num, area_num)

MATCH Function (pg.346)

The function that returns the relative position (such as 1,2, or 3) of a matched value in list. MATCH(lookup_value, lookup_array,match_type)