Busi 520 - Chap. 5
ISERROR Function
Checks whether a value is an error and returns either TRUE or FALSE Returns true if there is an error, false if there isn't
A LOOKUP function is used to retrieve a value from _____.
Column or row
T/F: Values in a "closest match" vlookup table must be sorted in descending order so that the VLOOKUP function can make accurate comparisons.
FALSE!
T/F: You can only use the VLOOKUP and the HLOOKUP formulas to retrieve data stored in lookup tables on the same worksheet.
FALSE!
T/F: 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! It can fall in a range of values, so use the approximate match lookup
T/F: The function =HLOOKUP(F12,$A$3:$D$10,3,TRUE) retrieves the value from the third column of the lookup table.
FALSE! It retrieves a value from the 3rd ROW since it is an HLOOKUP function VLOOKUP uses columns
T/F: 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 would multiply B5 of the Costs worksheet by C5 of the Forecast worksheet
T/F: 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! They do not need to be
T/F: The ISERR function refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
FALSE! Does not refer to #N/A
ISBLANK Function
Function that returns TRUE if the value is blank
HLOOKUP Function
Horizontal lookup function It searches horizontally across the top row of the lookup table and retrieves the value in the column you specify. =HLOOKUP(lookup value, table array, row index number, [range lookup]) =HLOOKUP(Value you want to look up, range where you want to lookup the value, the row number in the range containing the return value, Exact Match or Approximate Match - indicated as 0/FALSE or 1/TRUE).
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 since VLOOKUP searches from left to right
Index and Match Explanation
Nest match inside the Index function as the row number This will result in match finding the specified item and spitting out the corresponding row number to find the value
INDEX Function
Retrieves a value from a table based on the row and column numbers specified =INDEX(array, row number)
LOOKUP Function
Returns a value from either from a one-row or one-column function
The function =HLOOKUP(F16,Discounts,2,TRUE) retrieves the value in _____.
Row 2 in the table array
MATCH Function
Searches for a specified item in a range of cells, and then returns the relative position of that item in the range Ex: if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range. =MATCH(lookup value, lookup array)
T/F: 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!
T/F: To specify an "Exact Match" vlookup, enter the word False into the range_lookup argument of the vlookup function.
TRUE!
T/F: Ranges can be named in Excel to make it easier to reference the worksheet when using formulas or functions.
TRUE! Naming the ranges may make it easier to reference
VLOOKUP Function
Vertical lookup function It searches vertically across the first column of the lookup table and retrieves the value in the row you specify. =VLOOKUP(lookup value, table array, column index number, [range lookup]) =VLOOKUP(Value you want to look up, range where you want to lookup the value, the column number in the range containing the return value, Exact Match or Approximate Match - indicated as 0/FALSE or 1/TRUE).
