CH 5 Conceptual Review

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 is displayed

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.

=B13*VLOOKUP(B13,$B$3:$C$9,2,TRUE)

Write a formula to choose the name of the fifth day of the week from the list starting with Sunday, Monday, Tuesday....Saturday.

=CHOOSE(5,"Sunday","Monday","Tuesday", "Wednesday","Thursday","Friday","Saturday")

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(B3,GradesWorksheet!$A$6:$E$7,2,TRUE)

Write a formula in cell D13 the Pricing Worksheet to look up the correct delivery cost using the lookup table you created in Question 17. Write the formula so that it can be copied down the column.

=HLOOKUP(C13,Delivery!$B$2:$D$3,2,TRUE)

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 Horizontally.

Delivery Charges: Order Price: $0 $10 $40 Delivery Fee $6 $7.50 $0

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 is the difference between the LOOKUP function and the VLOOKUP or HLOOKUP function?

LOOKUP can retrieve data from a lookup table with a vertical or horizontal orientation

Reference and Lookup functions may not contain nested functions as arguments.

False

The formula =AVERAGE(CHOOSE(1,B12:D17,B22:D17)) averages the value 1 with the values in cells B12 to D17 and B22 to D17.

False

The lookup_value of a HLOOKUP function can be a contiguous cell range.

False

The result_vector of a LOOKUP function must be sorted in ascending order.

False

The row and column arguments in the INDEX function can be numeric values, Boolean values, or text.

False

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 tables differ as A3:E4 is organized in a descending order whereas A6:E7 is ascending. A3:E4 can not be evaluated using the HLOOKUP function.

Excel matches the lookup_value "tom" with the entry "TOM" in a lookup table.

True

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

The default range_lookup type for the VLOOKUP and HLOOKUP functions is TRUE.

True

The formula =INDEX((B2:D7,B12:D17,B22:D17),2,3,1) returns the value in cell D3.

True

The formula =MATCH(40,{10,20,40,50},0) returns the value 3.

True