1.Create a worksheet called Health Club Tables. In it, create an Excel Table for the plan code, name, and prices using Table 1 as reference. You can use this table to lookup the plans code. This table will have three columns. Name this Excel table PlanTable. 2. Reference the Plan Codes in the CustomersT to generate headers in cells I2# in the main table so that your filtering still works in case of changes of Plan Names used. To make sure the headings have the plan names and not plan codes, please use XLookup() to look up the plan name based on the plan code. Hint: Use helper columns to make your work easy. Once done the table should look like the one in Figure 1. 3. In Health Club Tables, add an Excel Table for the discount based on early payment using Table 2 as reference. Name the table DiscountTable. Table 2: Pre-payment discounts Number of Days in Advance Discount <70% 7and <135% 13and <2910% 2915% 2

CS 1100 Computer Science and Its Applications Topic 5: Using Look-up Functions 4. In the worksheet Customers, add columns for Discount, Fees, Total Due, and a filter table(The table from Column I to Column L in Figure 1) for each plan type (use the plan names). Figure 1 shows how they should be organized. 5. In this part you calculate the discount for each customer using two techniques based on a new function (XLOOKUP) and a traditional function (VLOOKUP). The XLOOKUP is more versatile and more general and you can easily use it for simulating VLOOKUP. (a) XLOOKUP Fill in the Discount column using XLOOKUP() to look up the discount in DiscountTable. You must use an approximate match on the number of days in advance. Hint: You can find the difference between the dates by subtracting Payment Date from Start Date -> (Start Date - Payment Date). (b) VLOOKUP Implement the same task using VLOOKUP. 6. Fill in the Fees column using XMATCH() and INDEX(). If the plan code is not an S, P, G or N, place $0in the Fees column. Use a function to do this step. Write a function called AttrToPrice_CSA which takes as input three columns. Person_Attr: is the column of person attributes that determine the price. The actual param- eter is the plan code. It is a column of CustomersT. AttrToPrice_Attr: is a column of the PlanTable. The actual parameter is the Code column. AttrToPrice_Price: is the column giving the price for each attribute. The actual parameter is the Monthly Price column. The function returns a column which gives the price each person has to pay based on her attribute. Use the following signature for the function AttrToPrice_CSA =LAMBDA(PersonT_Attr, AttrToPrice_Attr, AttrToPrice_Price,LET(...))... Using these three arguments in the signature gives flexibility because they allow us to accommodate many different table organizations. Although you use XMATCH() and INDEX() for the implementation of AttrToPrice_CSA, your function would be equally useful if you implemented it with XLOOKUP. You are encouraged to provide the XLOOKUP solution as well (2 bonus points). Carefully derive the implementation of AttrToPrice_CSA using the Function Template. 7. Calculate the total amount due after the discount in column Total Due. 8. Use IFERROR() to strengthen your formulas so that your model will catch incorrect data values for the plan code. 9. Using IF(), filter the data for each plan. Use XLOOKUP() to find the plans name based on the plan code and then match against the header using IF(). 10. Calculate the total number of customers in each plan. Calculate the percentages as well. 11. Use conditional formatting to highlight any plan that was wrongly coded. [Total due = $0] 12. Format the worksheet as shown in Figure 1.