Hokie Fitness Equipment & Accessories offers three different types of credit cards to its customers. It offers a Platinum card, a Red card, and an Everyday card. Customers who use one of the companys credit cards to pay for purchases receive a discount on product prices and shipping. Customers who have a Customer_ID of XXXXXXX do not have a company credit card.

Use an IF function and a VLOOKUP function in cell H5 of the SalesTrans sheet to display No_card if the Customer_ID is XXXXXXX; otherwise, the formula should find the Customer_ID in the Customer Data sheet and return the type of credit card that the customer has (either Platinum, Red or Everyday). Copy the formula in cell H5 to cell range H6:H210.

AutoSave ON APO... ACIS 2504 Excel Spreadsheet Project LastFirst.xlsx - Saved Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibrl (Body) v 11 ~ A ab Wrap Text General AYO G v SIE Conditional Format Cell Insert Delete Format Formatting es Table Styles FO) IN Analyze Data Pastc BIU 7 A Y = = = Y Merge & Center Y Find & v Sort & Filter Select H5 fx B D F G H T K L L M N O Hokie Fitness Equipment & Accessories, Inc. 2 Sales Transactions for September 2021 3 4 Transaction ID Card Type $0.00 7 1 Online 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Date Product ID Customer_ID Warehouse Bin Quantity Sold Sales Type 9/1/21 GB-NBS120 M062831 AVALUE! 2 Online 9/121 W.Y2R124 MC46295 AVA UEL 1 Oriline 9/1/21 ED SK1272 MD10BTG VALUEI Z 2 In store 9/1/21 GB-NBM271 MD27726 AVALUE! 9/1,21 ER-JP137 MRA1502 VALUE 2 Online 9/1/21 GB NE5453 XXXXCXX #VALUE! i in store 9/2/21 CM-SOL7343 MC22709 AVALUE! 1 Online 9/2/21 ER-SKI272 MC32107 VALUE 2 In-store 9/2/21 CM NDT220 MD24385 #VALUE! 1 In store 9/2/21 CM-NDT220 - MD56307 AVALUE! 1 in-store 9/2/21 WS-HF42.RO XXXXXXX VALUE 2 Oriline 9/2/21 EB J.5470 XXXXXXX #VALUE! 2 in store 9/3/21 F-GV361 MD25494 AVALUE! 1 Online 9/3/21 FT-C3362 MCS4593 VALUE 1 Online 9/3/21 WS-Y2R2GS MD2438! #VALUE! 2 In store 9/3/21 ED-JS170 ME64082 AVALUE! 1 In-store 9/3/21 HG-AFX107 XXXXXXX VALUE 1 Online 9/3/21 GB LAN843 XXXXXXX - #VALUE! 2 in store 9/4/21 CM-SOL7343 MD35019 AVALUE! 1 Online 9/4/21 WS-Y2R493 M36025 WB VALUE 3 In-store 9/4/21 FT-FVS4G2 MC39104 #VALUE! i Crline 9/9/21 GB-UM05 MC53802 AVALUE! 1 Online 9/4/21 CM-SCHRIO ME21567 VALUE 1 Online 9/4/21 FT GV3154 ME26013 AVALUE! 1 In store 9/9/21 CD-JF2402 MES8417 AVALUE 1 Online 9/421 CM-NDT425 MF6R107 VALUFI 1 Online 9/4/21 FT GV8154 MR92834 AVALUE! 1 Online 9/4/21 WL-FGR160 XOXXXOXXX AVALUE 1 Online 9/5/21 GENR5420 MA20736 VALUFI 2 In-store 9/5/21 WL FGR116 MC39104 AVALUE! 1 Online 9/5/21 WYRZES MD27726 AVALUE 3 in-store 9/5/21 W.Y2R176 MD33404 AVALLEI 1 line 9/5/21 ED SKI272 MD49:19 AVALUE! 2 Online 9/5/21 WB-HF3143 ME64082 AVALUE! 1 Orline 9/5/21 WL-BFX234 MEOR417 AVALUEI 1 Online 9/5/21 GB LIALS1 MR70078 VALUE! 2 In store 9/5/21 FT-SGF164 MR82864 AVALUE! 1 Orline 9/5/21 WL-FGR447 XXXXXXX VALUFI 1 1 in-store Category WALUE! WVALUEI WALUE! WALUE! WALUEI #VALUE! WALUE! WALUEI #VALUE! WALUE! WALUEI #VALUE! WALUE! WALLEI #VALUE! WALUE! WALLEI #VALUE! WALUE! WALLEI #VALUE! WALUE! WALUEI Cost per Unit Price per Unit $21.00 $18.50 $9.59 $24.00 $11.99 $21.00 $946.95 $9.59 $591.84 $591.84 $25.77 $5.99 $51.99 $97.47 $21.69 $5.99 $355.10 $27.89 $946.95 $18.59 $129.97 Total Sales Shipping Handling Cost Sales Taxes Total Due $0.00 $ $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 S0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 32 33 34 35 36 37 38 39 40 WALUE! WALUE! WALUEI #VALUE! WALUE! WALUEI #VALUE! WALUE! ! VALLEI #VALUE! WALUE! WVALLEI ALUT WALUE! WALUE! WALUEI $710.21 $51.99 $17.99 $946.95 $51.99 $147.96 $21.00 $236.73 $21.69 $18.59 $9.59 $24.77 $236.73 $32.85 $116.99 $165.71 41 42 Inventory Sales Trans Customer Data Miscinfo Monthly Payment Calculator Sales Pivot Employee Schedule Database Summary + Ready + 100% AutoSave ON APO... RO ACIS 2504 Excel Spreadsheet Project LastFirst.xlsx - Saved Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibrl (Body) v 11 v A A Wrap Text General X G AYO v FO) IN Analyze Data Paste A = = = Insert Format - Merge & Center Delete Y Y Conditional Format Cell Formatting es Table Styles Sort & v Filter Find & Select D11 fx c F G . I 2 K TL L M N O Q R S T U Credit Umit Messages Credit Remaining Message Shipping and Handling Rates Card Type Rate 3 No card 4.00% 4 Platinum 9.OON 5 5 Red 1.00% G Everyday 2.00% 7 2 9 Sales Tax Rate 6.30% 10 Number of Customers Assigned to 11 Cust_Service_Rep 13 12 13 Customer Service Representatives 14 Cust Serv Rep ID Cust Service Rep 15 CSR-134765 Lalunya Smith 16 CSR-725034 Andy Che 17 CSR 879452 Juana Torres 10 CSR-941725 Mustafa 19 Number of workdays 20 between payment dates 20 21 September Holiday 9/6/21 22 October Holiday 10/11/21 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 32 39 Inventory SalesTrans Customer Data Miscinfo Monthly Payment Calculator Sales Pivot Employee Schedule Database Summary + Ready + 100% % AutoSave ON APO... ACIS 2504 Excel Spreadsheet Project LastFirst.xlsx - Saved Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibrl (Body) v 11 ~ A ab Wrap Text General AYO G v SIE Conditional Format Cell Insert Delete Format Formatting es Table Styles FO) IN Analyze Data Pastc BIU 7 A Y = = = Y Merge & Center Y Find & v Sort & Filter Select H5 fx B D F G H T K L L M N O Hokie Fitness Equipment & Accessories, Inc. 2 Sales Transactions for September 2021 3 4 Transaction ID Card Type $0.00 7 1 Online 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Date Product ID Customer_ID Warehouse Bin Quantity Sold Sales Type 9/1/21 GB-NBS120 M062831 AVALUE! 2 Online 9/121 W.Y2R124 MC46295 AVA UEL 1 Oriline 9/1/21 ED SK1272 MD10BTG VALUEI Z 2 In store 9/1/21 GB-NBM271 MD27726 AVALUE! 9/1,21 ER-JP137 MRA1502 VALUE 2 Online 9/1/21 GB NE5453 XXXXCXX #VALUE! i in store 9/2/21 CM-SOL7343 MC22709 AVALUE! 1 Online 9/2/21 ER-SKI272 MC32107 VALUE 2 In-store 9/2/21 CM NDT220 MD24385 #VALUE! 1 In store 9/2/21 CM-NDT220 - MD56307 AVALUE! 1 in-store 9/2/21 WS-HF42.RO XXXXXXX VALUE 2 Oriline 9/2/21 EB J.5470 XXXXXXX #VALUE! 2 in store 9/3/21 F-GV361 MD25494 AVALUE! 1 Online 9/3/21 FT-C3362 MCS4593 VALUE 1 Online 9/3/21 WS-Y2R2GS MD2438! #VALUE! 2 In store 9/3/21 ED-JS170 ME64082 AVALUE! 1 In-store 9/3/21 HG-AFX107 XXXXXXX VALUE 1 Online 9/3/21 GB LAN843 XXXXXXX - #VALUE! 2 in store 9/4/21 CM-SOL7343 MD35019 AVALUE! 1 Online 9/4/21 WS-Y2R493 M36025 WB VALUE 3 In-store 9/4/21 FT-FVS4G2 MC39104 #VALUE! i Crline 9/9/21 GB-UM05 MC53802 AVALUE! 1 Online 9/4/21 CM-SCHRIO ME21567 VALUE 1 Online 9/4/21 FT GV3154 ME26013 AVALUE! 1 In store 9/9/21 CD-JF2402 MES8417 AVALUE 1 Online 9/421 CM-NDT425 MF6R107 VALUFI 1 Online 9/4/21 FT GV8154 MR92834 AVALUE! 1 Online 9/4/21 WL-FGR160 XOXXXOXXX AVALUE 1 Online 9/5/21 GENR5420 MA20736 VALUFI 2 In-store 9/5/21 WL FGR116 MC39104 AVALUE! 1 Online 9/5/21 WYRZES MD27726 AVALUE 3 in-store 9/5/21 W.Y2R176 MD33404 AVALLEI 1 line 9/5/21 ED SKI272 MD49:19 AVALUE! 2 Online 9/5/21 WB-HF3143 ME64082 AVALUE! 1 Orline 9/5/21 WL-BFX234 MEOR417 AVALUEI 1 Online 9/5/21 GB LIALS1 MR70078 VALUE! 2 In store 9/5/21 FT-SGF164 MR82864 AVALUE! 1 Orline 9/5/21 WL-FGR447 XXXXXXX VALUFI 1 1 in-store Category WALUE! WVALUEI WALUE! WALUE! WALUEI #VALUE! WALUE! WALUEI #VALUE! WALUE! WALUEI #VALUE! WALUE! WALLEI #VALUE! WALUE! WALLEI #VALUE! WALUE! WALLEI #VALUE! WALUE! WALUEI Cost per Unit Price per Unit $21.00 $18.50 $9.59 $24.00 $11.99 $21.00 $946.95 $9.59 $591.84 $591.84 $25.77 $5.99 $51.99 $97.47 $21.69 $5.99 $355.10 $27.89 $946.95 $18.59 $129.97 Total Sales Shipping Handling Cost Sales Taxes Total Due $0.00 $ $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 S0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 32 33 34 35 36 37 38 39 40 WALUE! WALUE! WALUEI #VALUE! WALUE! WALUEI #VALUE! WALUE! ! VALLEI #VALUE! WALUE! WVALLEI ALUT WALUE! WALUE! WALUEI $710.21 $51.99 $17.99 $946.95 $51.99 $147.96 $21.00 $236.73 $21.69 $18.59 $9.59 $24.77 $236.73 $32.85 $116.99 $165.71 41 42 Inventory Sales Trans Customer Data Miscinfo Monthly Payment Calculator Sales Pivot Employee Schedule Database Summary + Ready + 100% AutoSave ON APO... RO ACIS 2504 Excel Spreadsheet Project LastFirst.xlsx - Saved Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibrl (Body) v 11 v A A Wrap Text General X G AYO v FO) IN Analyze Data Paste A = = = Insert Format - Merge & Center Delete Y Y Conditional Format Cell Formatting es Table Styles Sort & v Filter Find & Select D11 fx c F G . I 2 K TL L M N O Q R S T U Credit Umit Messages Credit Remaining Message Shipping and Handling Rates Card Type Rate 3 No card 4.00% 4 Platinum 9.OON 5 5 Red 1.00% G Everyday 2.00% 7 2 9 Sales Tax Rate 6.30% 10 Number of Customers Assigned to 11 Cust_Service_Rep 13 12 13 Customer Service Representatives 14 Cust Serv Rep ID Cust Service Rep 15 CSR-134765 Lalunya Smith 16 CSR-725034 Andy Che 17 CSR 879452 Juana Torres 10 CSR-941725 Mustafa 19 Number of workdays 20 between payment dates 20 21 September Holiday 9/6/21 22 October Holiday 10/11/21 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 32 39 Inventory SalesTrans Customer Data Miscinfo Monthly Payment Calculator Sales Pivot Employee Schedule Database Summary + Ready + 100% %