Ch. 5 Retrieving Data

match type in MATCH function

-1: less than 0: exact match 1: greater than

MATCH match_type

0: exact or displays #N/A 1: greatest value that is less than or equal to the lookup value -1: smallest value that is greater than or equal to the lookup value

creating the vlookup table

1. lookup values need to be in the first column 2. for teh closest-match vlookup table, use the min values of each range in the first column 3. the breakpoints in a closest-match vlookup table need to be in ascending order - should accommodate all possible values without giving errors - the table needs to be a single range, not broken up into two separate tables - if no max value for highest range, enter "& above"

creating the vlookup table array

1. lookup values need to be in the first column 2. for the closest match, use the minimum values of each range in the first column of the table 3. the breakpoints in the closest match table need to be in ascending order

Creating an exact match vlookup table

1. the thing you're looking up goes in the first column of the table array 2. your lookup_values must exactly match the key data in column 1 of your table array

In order to correctly use an "exact match" VLOOKUP function, the values in the first column of the lookup table must be sorted in ascending order.

False

The ISERROR function refers to any error value except #N/A.

False

The range_lookup argument of the VLOOKUP and HLOOKUP functions is the range of values to be looked up in order to determine the function's result.

False

Values in a "closest match" vlookup table must be sorted in descending order so that the VLOOKUP function can make accurate comparisons.

False

When using VLOOKUP or HLOOKUP to look up a number that can fall within a range of values, you should be using an "Exact Match" lookup.

False

You can only use the VLOOKUP and the HLOOKUP formulas to retrieve data stored in lookup tables on the same worksheet.

False

When entered in a worksheet named Forecast, the formula =Costs!B5*C5 multiples the value in cell B5 of the "Costs" worksheet by the value in cell C5 of the "Costs" worksheet.

False; It multiplies the B5 value from the "Costs" worksheet by the value in C5 of the "Forecast" worksheet

The function =HLOOKUP(F12,$A$3:$D$10,3,TRUE) retrieves the value from the third column of the lookup table.

False; It retrieves the value from the third ROW of the lookup table

The ISERR function refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).

False; it doesn't refer to #N/A (ISERROR refers to all)

Use the _____ function to retrieve a value from a table based on the row and column numbers specified.

INDEX; the Index function returns the value of an element in a table or an array selected by the row and column number indexes

Use _____ to check whether a specified reference or formula returns an empty cell.

ISBLANK

MATCH

MATCH(lookup_value,lookup_array,match_type)

Ranges can be named in Excel to make it easier to reference the worksheet when using formulas or functions.

True

The lookup_value argument in a VLOOKUP or HLOOKUP function is often a number, text, a logical value, or a cell reference...but the lookup_value argument can also be a formula.

True

To specify an "Exact Match" vlookup, enter the word False into the range_lookup argument of the vlookup function.

True

closest match vlookup

True

VLOOKUP function

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

The "V" in VLOOKUP stands for _____.

Vertical

COLUMN

Vlookup with the COLUMNS(...) function used to determine the col_index_num. Unlike the hard-coded col_index_num, this method is flexible because it allows columns to be inserted into or deleted from within the vlookup table without needing to edit the VLOOKUP formula. The col_index_num adjusts automatically to yield the correct vlookup answer.

A LOOKUP function is used to retrieve a value from _____.

a column or row

MATCH lookup_array

a one dimensional horizontal or vertical list which can be a range or a list of values enclosed in braces

CHOOSE

a reference and lookup function that can return a value or a range for up to 254 different values =CHOOSE(index_num,value1,value2)

INDEX

array, row_number, col_number

The number of the column containing the data you want to retrieve for the VLOOKUP function is the _____.

col_index_num

MATCH function

designed to return the relative position of an item in a list =MATCH(lookup_value,lookup_array,match_type)

exact match vlookup

false

When using a VLOOKUP function, the first column of the lookup table that contains the data you are comparing to your "lookup_value" is/are called the _____.

key data

The VLOOKUP function "looks up" a value and evaluates (compares) it against values in a lookup_table (the "key data") in order to return a matching "answer." When constructing a table_array to be used as lookup reference table, the "key data" should be placed in the _____.

leftmost column; the VLOOKUP function returns a matching answer from the table_array by pulling it from the column specified by col_index_num

A data list that categorizes values for retrieval is a(n) _____ table.

lookup

the table to refer to when looking up a value

lookup table (table array)

the data in the first column to be looked up

lookup value

For the VLOOKUP function, the _____ is the data to be looked up.

lookup_value

Index-Match

more flexible than v/h lookup

IS functions

nine functions that test a value or cell reference and then return a T/F value depending on the results; often used in formulas to test the outcome of a calculation and when combined w the IF function, they help locate data-entry errors

true/false in the vlookup

range lookup true - approximate match false - exact match

The _____ argument of the VLOOKUP function is the type of lookup...the value should be either TRUE or FALSE.

range_lookup

The function =HLOOKUP(F16,Discounts,2,TRUE) retrieves the value in _____.

row 2 of the table_array

INDEX reference

the range containing the data you want to find. the range can be a contiguous range or a set of nonadjacent ranges

looking up text

the range_lookup has to be FALSE (exact match)

MATCH lookup_value

the value you want to match in the list, which can be a constant, text, the value FALSE, or a formula that returns a numeric, text, or boolean value

The ISBLANK function returns a value of TRUE or FALSE.

true

when to use index function

two or three dimensional tables; it returns the value in a table based on the row and column numbers specified

vlookup and match

used together to determine col_index_num; using match is more flexible than columns

ISLOGICAL

value refers to a logical value

ISNUMBER

value refers to a number

ISREF

value refers to a reference

ISBLANK

value refers to an empty cell

ISERROR

value refers to any error value (#N/A, #VALUE!, #REF! #DIV/0!, #NUM!, #NAME?, or #NULL!)

ISERR

value refers to any error value except #N/A

ISNONTEXT

value refers to any item that is not text (this function returns TRUE if the value refers to a blank cell)

ISTEXT

value refers to text

ISNA

value refers to the #N/A error value

When to use Vlookup

when you have two or more choices to choose form in a list of data (arranged vertically). although using nested IF statements will often work when you have a simple list of matching values, VLOOKUP is simpler and more flexible