VLOOKUP, HLOOKUP, IFERROR and Data Validation Starter File: Chapter 3 AES Assignment 3 Difficulty: Level 3 Difficult

Amy Woodward is the manager of the Lane Community College Titan Bookstore which sells products to loyal alums of Lane Community College. Products offered by Amy on the website range from tee shirts and backpacks to mugs and blankets and all feature the schools logo. Alice has a large, steady base of customers who find these uniquely designed products a great reminder of their college days. To ensure the timely recipe of payments, Alice wants you to use Excel to create an invoice she can use for each customer transaction. Complete the following:

1. Open the file VLOOKUP HLOOKUP IFERROR and Data Validation.xlsx

2. In the Data worksheet name the following ranges a. Shipping cost table E2:I3, ShippingCost b. Product pricing table A3:B28, Products c. Items list: A3:A28, Items d. Sales Tax table D6:E7, Sales Tax

3. In the States worksheet: a. name the range B2:B52, StateAbbr b. name the cell H37 Subtotl (intentionally misspelled because Excel has a subtotal function) In the Invoice worksheet:

4. Name the Subtotal cell in H37 OrderTotal

5. Use a data validation list in cell D12 to bring up the state abbreviation. (Hint: Select the entire range before setting the validation rule)

6. Use data validation lists in the range B16:B36 to bring up the list of products in the cells. (Hint: Select the entire range before setting the validation rule.

7. In the Per Unit Column G (the range G16:G36) use a VLOOKUP function to retrieve the per-unit price of each ordered product from the Product Pricing data in the Product and Shipping worksheet. (Hint: Use the defined name Products that was assigned to the Product Pricing Data.) Dont worry about the N/A error we will correct that in the next step.

8. Modify the formula in the Per Unit column by combining the IFERROR function with the VLOOKUP function to display either the per-unit price or a blank cell. Format the column appropriately.

9. In the Total Column (the range H16:H36), enter the formula to calculate the total charge for that row (Qty * price per unit). After you have determined your formula is working correctly, use an IFERROR to function to display either the total charge or a blank cell

10. In the Subtotal cell (cell H37), add a formula to sum the Total column. Format the cell appropriately.

11. In the Sales Tax cell (cell H38) enter a formula using a nested IF that uses an OR to test D12 to see if is it CA or WA. For the true result use a VLOOKUP that looks at the state abbreviation in D12 and compares it to the named range SalesTax to return the sales tax percent. Multiply the result of your nested IF function by the Subtotal to calculate total sales tax due.

12. In the Shipping cell H39, use an HLOOKUP function to look up the shipping cost from the Shipping Cost table in the Data worksheet based on the Subtotal in sell H37. If the subtotal is 0, the shipping cost should display 0. (Hint: use an IF function to nest your HLOOKUP to return a value of 0 if Subtotal is 0).

13. In the Total Due cell (cell H40), calculate the invoice total by entering a formula that adds the values in the Subtotal, Sales Tax, and Shipping cells. Format this cell appropriately.