1 2 B E F GH J | K | L M N Built-tough Bulletin Boards Built-tough Boards sells outside bulletin boards used to display community information on the outside of buildings. They sell blue and red bulletin boards which they will deliver regionally up to 1000 miles. While they offer a discount for purchasing in bulk, they require customers to buy at least two bulletin boards at a time. The table below lists 25 recent sales. Each order lists the color of the boards ordered, the number to be shipped with the order, and the distance of the delivery in miles. Complete the table by using the VLOOKUP and HLOOKUP functions in Excel to calculate the number of days it will take for delivery, the delivery price, and the price of the signs for each order. R 3 4 5 6 7 Order # Color # Shipped Distance # Days Delivery Price Total 8 1 Red 4 845 Delivery Information Miles #Days Price Product Pricing #Shipped Blue Red 9 2 Blue 10 421 0 1 $ 20.00 10 3 Red 5 5 50 2 $ 30.00 11 4 Red 7 883 100 3 $ 50.00 2 $ 300.00 $ 500.00 3 $ 400.00 $ 700.00 4 $ 500.00 $ 800.00 12 5 Red 4 181 200 5 $ 75.00 13 6 Blue 9 117 400 7 $150.00 14 7 Red 7 867 800 8 $400.00 15 8 Blue 9 17 16 9 Blue 6 646 17 10 Blue 2 486 18 11 Red 9 195 19 12 Blue 6 374 20 13 Blue 4 74 21 14 Blue 7 17 22 15 Blue 6 822 23 16 Blue 9 940 24 17 Red 4 140 25 18 Blue 3 56 26 19 Red 10 823 27 20 Blue 2 705 28 21 Red 7 54 29 22 Red 3 35 5 $ 600.00 $ 975.00 6 $ 700.00 $1,100.00 $1,200.00 8 $ 900.00 $1,350.00 9 $1,000.00 10 $1,100.00 $1,550.00 Hint: This is a challenging task, but it shows the power of HLOOKUP (and VLOOKUP as well). Use the billboard color as your "lookup_value". The "lookup_value" is used to find a match on the lookup table. The "table_array" must start in row 9 because that is where "Blue" and "Red" are located. When using HLOOKUP, Excel always looks for a match to the "lookup_value" in the first row of the "table_array". The "row_index_number" is the row of the "table_array" from which you return the appropriate pricing. In this case, the lookup table is arranged so that the row number of the table corresponds with the quantity shipped (the price for buying 2 billboards is on the second row of the reference table). Therefore, you can use the "#Shipped" from the information you have on the table as the "row_index_number". 7 $ 800.00 $1,425.00 30 23 Red 4 684 31 24 Red 9 60 32 25 Red 6 86 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50