CGS Chapter 5
T or F. Excel matches the lookup value "tom" with the entry "TOM" in a lookup table.
True
T or F. In a VLOOKUP formula with a TRUE lookup type, the first column of the lookup table referenced must be in ascending order to retrieve the correct value.
True
T or F. The default range lookup type for the VLOOKUP and HLOOKUP functions is TRUE.
True
T or F. The formula =INDEX((B2:D7,B12:D17,B22:D17),2,3,1) returns the value in cell D3.
True
T or F. The result vector of a LOOKUP function must be assorted in ascending order.
True
What happens when Excel is solving a HLOOKUP formula with a FALSE range lookup type and does not find an exact match in the lookup table.
#N/A
Write a formula to choose the name of the third day of the week from the list starting with Sunday, Monday, Tuesday,...Saturday.
=CHOOSE(3,"SUNDAY","MONDAY","TUESDAY","WEDNESDAY" ,"THURSDAY","FRIDAY","SATURDAY")
Which excel function should you use when you want to look up a value from a two-dimensional table, where both the columns and rows can be varied?
INDEX
What function returns the relative position of an item from a list?
MATCH
Explain the difference between the lookup table in cells A3:E4 of the Grades worksheet and the lookup table in cells A6:E7 in the same worksheet.
The lookup table A3:E4 is sorted in descending order, whereas the lookup table in A6:E7 is sorted in ascending order, which allows you to use to use the HLOOKUP function with a true lookup type.
As shown in the Grades worksheet, final grades are determined using the following grading scheme: a. Students earning over 900 points receive an A. b. Students earning less than 900 points but at least 800 points receive a B. c. Students earning less than 800 points but at least 700 points receive a C. d. Students earning less than 700 points but at least 600 points receive a D. e. Students earning less than 600 points receive an F. Write a formula in cell C3 in the Scores worksheet that determines the final grade for the first student based on the grading scheme. Use the appropriate Reference and Lookup function and write the formula so that it can be copied down the column.
=HLOOKUP(B2,$A$5:$E$6,2,TRUE)
Write a formula in Cell D13 in the Pricing worksheet to lookup the correct delivery cost using the lookup table you created in Question 17. Write the formula so it can be copied down the column.
=VLOOKUP(A2,$A3:$B3,3,True)
Write a formula in cell C13 in the pricing worksheet to determine the total cost of making copies for this order(275 copies). Write the formula so that it works when copied into cells C14:C15.
=VLOOKUP(B13,$B3:$C9,2,TRUE)*B13
What is the difference between the LOOKUP function and the VLOOKUP or HLOOKUP function?
A VLOOKUP uses the left most column as the lookup vector. A HLOOKUP uses the top row as the lookup vector. A LOOKUP uses a specified column or row and is backwards compatible.
T or F. Reference and Lookup functions may not contain nested function as arguments.
False
T or F. The formula =AVERAGE(CHOOSE(1,B12:D17,B22:D17)) average the values 1 with the values in cell B12 to D17 and B22 to D17.
False
T or F. The look up value of a HLOOKUP function can be a contiguous cell range
False
T or F. The row and column arguments in the INDEX function can be numeric values, Boolean values, or text.
False
The delivery charges used in the Delivery worksheet are as follows: a. For orders under $10, there is a $6 delivery fee. b. For orders at least $10 but less tha $40, there is a $7.50 delivery fee. c. For orders over $40, delivery is free of charge. Create a lookup table in the Delivery worksheet, so that you can use a lookup function to calculate the delivery cost for each order. Organize the table in a horizontal format, as shown here:
Fill in the chart with the rows equaling>=20, >=50, and 5.00, 8.00, and 0 for the respective prices
T or F. The formula =MATCH(40,{10,20,40,50},0) returns the value 3.
true
