NOTE: Throughout this lab, every time a screenshot is requested, use your computer's screenshot tool, and paste each screenshot to the same Word document. Label each screenshot in accordance to what is noted in the lab. This document with all of the screenshots included should be uploaded through Connect as a Word or PDF document when you have reached the final step of the lab. In this lab, you will: Required: 1. Create a column titled Customer_St in the Sales_Transactions table. Use VLOOKUP to match the Customer_ID from the Sales_Transaction table to the Customer_ID in the Customers table, and pull in the data from the column containing Customer_St. 2. Create a column titled Customer_City. Use VLOOKUP to match the Customer_ID from the Sales_Transactions table to the Customer_ID in the Customers table, and pull in the data from the column containing Customer_St in the Sales_Transactions. For a challenge, create both VLOOKUPs without using your mouse, just use your keyboard to reference column and table names! 3. Return to the PivotTable you created, and click Refresh from the Analyze tab on the ribbon to view the two new attributes in the field list, Customer_St and Customer_City. Ask the Question: How can we pull in descriptive attributes from separate tables into the transaction table that we wish to use for analysis? Master the Data: Continue working with the same data file (Lab 4-3 Data). Add in two new columns, one for Customer_St another for Customer_City. Software needed Excel Screen capture tool (Windows: Snipping Tool; Mac: Cmd+Shift+4) Data: Excel File Lab 4-3 Data.xlsx. Perform the Analysis: Refer to Lab 4-3 Alternate in the text for instructions and Lab 4-3 steps for each the of lab parts. Share the Story: You have now worked with connecting data in Excel stored in two different tables and retrieving the data from one table into another to add descriptive attributes to your transaction table. Part 1: Upload Your Files Part 2: Assessment Sales_Order_Quantity_Sold 12 8 5 6 5 2 2 o 8 11 Product_Sale_Price 105 85 105 120 95 95 105 105 105 100 105 100 100 100 95 85 3 7 3 1 8 7 9 85 90 120 Sales_Order_ID 20062 20168 20383 20504 20564 20140 20310 2010 2030R 20308 20009 LUIS 20233 20163 20224 LUCAS 20199 20199 20291 20451 20451 20012 004 20494 20494 10 20537 20250 20251 20003 20241 20253 20005 20005 20090 20033 20055 20211 20211 20338 20395 20393 20097 20418 TO 20543 20277 LUET 20406 Sales_Order_Date Sales_Employee_ID 11/22/19 1007 12/27/19 1007 3/4/20 1007 4/28/20 1007 12/20/19 1006 2/11/20 1007 2/11/20 1006 2/1100 2/11/201006 41 11/2/19 1006 11/21 2006 1/16/20 1006 7107201000 12/25/19 1006 1/13/20 1006 we de 1/7/20 1007 2/4/20 1007 3/24/20 1006 3/24/20 1006 11/4/19 1006 4/8/20 1006 4/8/20 1006 4/19/20 1007 ver 1/22/20 1006 way 1/22/20 1007 Moc 11/1/19 1006 ..... 1/19/20 1007 1/22/20 1007 1007 11/1/19 1006 2011 11/1/19 1006 12/1/19 1006 11/12/19 1007 1/10/20 1007 1/10/20 1007 2/22/20 1006 3/7/20 1007 12/4/19 1007 3/14/20 1006 4/20/20 1006 4720 1/29/20 1006 3/10/20 1007 1000 12/25/19 1007 1/14/20 1006 3/3/20 1006 11/21/19 1006 11/29/19 1007 Customer_ID 2001 2001 2001 2001 2002 2004 2002 2004 2004 2005 2009 suo 2006 2007 2007 2008 2000 2008 . 2008 we 2008 2009 2009 2009 2009 2009 2010 2014 2010 . 2011 2011 2011 2012 2012 2012 2013 2015 2013 2015 2013 2015 2013 2013 2014 2016 2 1 6 2 6 Product_Code 2004 2005 2004 2009 2002 2001 2001 2004 2009 2004 2003 2004 2003 2003 2009 2001 2005 200 2005 2006 2002 2005 200 2005 2004 2004 2005 2004 2006 2004 2005 2006 2001 2002 2003 2005 2006 2006 2005 2005 2005 2003 2005 2003 2006 2003 2006 2000 2005 200 2006 zoo 2001 2001 2003 2001 85 85 105 10 105 85 105 5 11 8 2 10 90 . 105 85 9 90 8 10 1 1 12 12 95 120 100 90 an 30 85 85 85 85 100 100 100 90 90 24 100 6 9 2016 2016 2017 9 12 12 3 1 1 a 4 12 90 2017 2019 20161 9 20229 20379 20056 20080 12 85 90 30 95 95 100 2019 2020 2021 2021 8 11 95 3 4 6 2005 2001 2004 2003 2002 2001 2002 4 4 85 95 105 100 120 95 120 105 105 100 1 6 2004 5 2004 4 1 1 2003 2004 105 100 20305 20305 20305 20312 20031 20223 20230 20266 20452 www 20454 201454 20265 20404 20404 20471 20513 20218 20910 20307 20307 20227 20438 20375 20465 20120 20496 2003 2006 2003 2001 1 1 3 2 6 10 90 100 100 95 2004 4 4 2/10/20 1007 2/10/20 1007 2/10/20 1007 2/12/20 1006 11/12/19 1006 1/13/20 1006 1/15/20 1006 1/27/20 1006 3/25/20 1007 3/25/20 1006 1/27/20 1006 3/9/20 1006 3/9/20 1006 3/29/20 1007 4/13/20 1007 10.1002 1/12/20 1007 24/ 1007 2/11/20 1007 tana 2/11/20 1007 1/14/20 1006 3/18/20 1007 3/2/20 1007 3/28/20 1006 12/12/19 1006 4/8/20 1006 11/7/19 1006 11/8/19 1007 11/22/19 1006 1/1/20 1006 1/1/20 1006 1/24/20 1006 11/20/19 1006 11/30/19 1006 9 9 2 4 20 2004 2005 2001 2006 2006 2004 2005 2004 2003 2004 11 2021 2021 2021 2021 2022 2022 2022 2022 2022 2022 2023 2024 2024 2025 2025 2026 3027 2027 2027 2028 2028 2029 2029 2031 2031 2032 2033 2034 2034 2034 2034 2035 2035 2035 2036 2036 2036 2037 2037 2037 2037 2037 2038 2038 2038 8 10 5 105 105 85 95 90 90 105 85 105 100 105 100 120 95 105 20020 6 11 20024 2003 2002 2001 4 10 20063 20183 20183 20256 20053 US 20086 7 5 2004 105 2 11 85 20202 1/8/20 1006 85 7 7 6 3 3 105 20179 20453 200C 20495 an 20040 2000 20098 4 2 90 05 85 100 2004 2005 2005 2004 2006 2005 2003 2003 2006 2006 2003 2003 2002 2005 12/30/19 1006 3/25/20 1007 4/8/20 1007 11/14/19 1007 1007 12/4/19 1007 1/31/20 1007 2/17/20 1007 3/4/20 1007 11/4/19 1007 11/4/19 1007 12/1/19 1007 4 4 20283 20328 11 10 20384 100 90 90 100 100 120 85 2 20013 20013 20089 4 10 11 3 2039 2039 2039 2040 2010 2010 11 4 7 7. 7 2040 105 95 120 10 90 90 95 95 105 102 100 90 105 105 2 4 9 11 1 2010 2010 2040 2041 2042 2042 2043 2043 2043 2043 2043 2043 2043 2044 2044 2045 12 12 10 95 85 . 100 105 10 2 1 9 9 12 8 95 105 105 100 100 105 2045 6 20055 20440 20516 20516 20290 20280 20280 20378 2010S 20403 20416 20138 20142 20559 0 20017 1 20043 camer 2 20200 3 20200 4 20457 2016 5 20461 SOCAR 6 20545 7 20407 8 20407 9 20357 0 20535 1 20314 2 20450 3 20368 4 20106 5 20126 620126 7 20217 8 20151 9 20518 0 20282 1 20285 2 20424 3 20460 4 20262 5 20023 620068 7 20249 820539 9 20037 20037 1 20001 2 20137 11/20/19 1007 3/19/20 1007 4/13/20 1006 1/30/20 1006 1/30/20 1006 3/3/20 1007 3/9/20 1006 3/9/20 1006 3/13/20 1006 190 12/19/19 1007 21200 12/20/19 1007 4/25/20 1007 ver 11/7/19 1006 11/16/19 1006 wedd 1/7/20 1007 . 1/7/20 1007 Jacinto 3/26/20 1006 2/27/201007 3/27/20 1007 1007 4/21/20 1007 3/10/20 1006 3/10/20 1006 2/27/20 1006 4/19/20 1007 14100 2/13/20 1007 . 3/24/20 1007 2/29/20 1007 12/7/19 1006 12/16/19 1006 12/16/19 1006 1/12/20 1007 12/22/19 1007 4/14/20 1007 1/31/20 1006 vo 2/1/20 1007 3/16/20 1006 3/27/20 1006 1/26/20 1007 11/8/19 1007 11/24/19 1007 1/22/20 1006 4/19/20 1006 11/13/19 1007 11/13/19 1007 11/1/19 1006 12/18/19 1006 2004 2001 2002 2006 2001 2001 2004 2003 2006 2004 2004 2001 2005 2003 Zoo 2004 2002 2001 2004 5002 2003 2004 2006 2005 2003 2004 2003 2003 2004 2004 Kons 2005 2003 2004 2001 2002 2003 2004 2003 2005 2005 2004 2004 2004 2004 2005 2001 2003 2 90 85 os 100 100 105 100 100 105 9 11 1 6 105 85 9 9 11 8 2046 2046 2047 2048 2048 2048 2048 2049 20 2049 2050 2050 2050 2050 2051 2052 2052 2053 8 7 2 10 6 12 100 105 95 120 100 105 100 85 or 85 2 9 105 105 105 8 2053 105 2054 10 3 3 3 105 85 2054 2056 2056 10 95 10 100 133 20155 F2056 2003 100 2056 2003 134 20439 135 20261 136 20492 8 1 6 100 2057 2003 100 2057 2003 10 100 137 20093 2058 2002 1 120 138 20325 2058 2003 7 100 139 20124 2059 2004 11 105 2060 2005 6 85 140 20059 141 20103 2060 2003 7 100 12/23/19 1006 3/19/20 1007 1/25/20 1006 4/8/20 1006 12/2/19 1007 2/15/20 1006 12/15/19 1007 11/22/19 1006 12/5/19 1006 1/28/20 1007 4/7/20 1006 4/17/20 1007 1/6/20 1007 1/19/20 1006 3/26/20 1007 3/19/20 1007 4/10/20 1006 4/10/20 1006 142 20271 2060 2005 11 85 143 20491 2060 2003 3 100 144 20532 2060 2003 11 100 145 20192 2061 2004 12 105 146 20242 2061 2004 3 105 147 20458 2061 2003 11 100 148 20441 2062 2003 10 100 149 20503 2062 2005 6 85 2062 2004 2 105 150 20503 151 152 153 1 Customer_IC Customer_s Customer_zi 2 2001 IA 50320 3 2002 TX 75226 Business_Name Customer_Addres Customer_City Beverage Distributors 3221 SE 14th Street Des Moines Deep Ellum Brewing Company 2823 St Louis Street Dallas Schatz Distributing Co. 3140 S. 28th Steet Kansas City Arkansas Craft Distributors 1515 E. 4th Street Little Rock Fleck Sales Co. 11125 High Life Ct SW Cedar Rapids Wholesale Liquor 4720 S Broadway St Wichita 4 2003 KS 66106 5 2004 6 2005 7 2006 AR IA 72202 52404 67216 KS 8 2007 TX 76111 9 2008 NE 68117 10 2009 TX 75207 11 2010 MO 65201 12 2011 13 2012 14 2013 MO TN MO 64108 38134 65201 15 2014 LA 70125 38118 16 2015 TN 17 2016 LA 70121 Texas Wholesale 550 North Beach Street Fort Worth D&J Beverage Services 4740 G. Street Omaha Andrews Distributing 2730 Distributing Company Company Dallas 111 South 9th Street Craft Beer Cellar Suite #10 Columbia 1903 Woodland United Beverage Co. Avenue Kansas City Star Distributors 16290 Shelby View Dr. Memphis Golden Barrel Inc 1501 1-70 Complex Ct Columbia Louisiana Specialty Drinks 1603 S Gayoso St New Orleans AS Barboro Inc 5020 Tuggle Rd Memphis Republic National Distributing Company 809 Jefferson Hwy New Orleans MBA Tulsa Beverage Co 510 W. Skelly Drive Tulsa Duff Beer 1319 S. Houston Road Pasadena Faust Distributing Company 10040 East Freeway Houston Glazer's 6201 Stilwell Street Kansas City LDF Sales & 10718 E. Marshall Distributing Street Tulsa Wil Fischer Companies 3539 W Farm Rd 142 Springfield Quality Beverage Company 7029 E. Reading Place Tulsa Brown Distributing 8711 Johnny Morris Inc Road Austin Boardwalk 4157 S. 72nd East Distribution Avenue Tulsa Texas Quality Beverage 13300 Park Vista Blvd Fort Worth lowa Beverage Systems 2115 NE 58th Avenue Des Moines 18 2017 OK 74107 19 2018 TX 77502 TX 20 2019 21 2020 77029 64120 MO 22 2021 OK 74116 23 2022 MO 65807 24 2023 OK 74115 25 2024 TX 78724 26 2025 OK 74145 27 2026 TX 76177 28 2027 IA 50313 29 2028 OK 73118 30 2029 KS 66609 MO 31 2030 32 2031 65807 52240 IA 33 2032 TX 72220 34 2033 AR 72901 35 2034 NE 68138 36 2035 KS 66219 37 2036 NE 68138 38 2037 NE 68127 39 2038 OK 73107 40 2039 TX 75235 Beer Distributors of 6301 Wateford Blvd Oklahoma #105 Oklahoma City 4235 SW Burlingame Strathman Sales Co. Road Topeka Heart of America Beverage 2860 S Austin Ave Springfield Evans Distributing 3750 Liberty Drive lowa City Texas Specialty Beverage 2512 Program Drive Dallas Belle Point Beverages Inc. 1 Belle Point Place Fort Smith Quality Brands of Omaha 13255 Centech Road Omaha Wil Fischer Distributing of Kansas 17501 W 98th Street Lenexa Premier Midwest Beverage 10367 S. 134th Street Omaha Johnson Brothers of Nebraska 9320 Street Omaha 421 North Portland Capital Distributing Avenue Oklahoma City Ben E. Keith 1805 Record Crossing Company Road Dallas Burford Distributing 925 H Street South Fort Smith Standard Beverage Corporation 2416 E 37th St N Wichita Arkansas Craft Distributors 1605 W. Shady Grove Springdale 7777 Washington Silver Eagle Avenue Houston Grand Wholesale Liquor Inc 609 S Grand St. Amarillo Premium Brands of NW Arkansas 1032 S. Razorback Road Fayetteville Ben E. Keith Company 2300 N Lakeside Dr Amarillo City Wholesale Liquor Co 4340 Washington Ave New Orleans Reed Beverage Distributor 3333 SE 3rd Ave Amarillo Doll Distributing Inc. 3501 23rd Avenue Council Bluffs Broadway Beer 2304 North Broadway Distributing Co Avenue Oklahoma City Capitol Wright 3834 Promontory South Austin Point Drive Austin 41 2040 AR 72901 42 2041 KS 67219 43 2042 AR 72764 44 2043 TX 77007 45 2044 TX 79104 46 2045 AR 72701 47 2046 TX 79108 48 2047 LA 70125 49 2048 TX 79104 50 2049 IA 51501 51 2050 OK 73103 52 2051 TX 78744 53 2052 Sooner Beer Co 605 North Tulsa Avenue Oklahoma City OK 73107 53 2052 OK 605 North Tulsa Avenue Oklahoma City 3002 Chapel Hill Road Stillwater 73107 74074 54 2053 OK 3300 Pleasant Valley Lane 55 2054 Arlington TX 76015 56 2055 203 E. 11th Avenue North Kansas City MO 64116 Sooner Beer CO Barn Spirits American Wholesale Distribution North Kansas City Beverage Co. General Distributors BDT Beverage Standard Beverage Corporation High Life Sales Co. Wholesale Beer Distributors Nauser Beverage KS 67211 57 2056 58 2057 800 E Indianapolis St. Wichita 2115 Spicer Cove #113 Memphis TN 38134 Lawrence KS 66049 59 2058 60 2059 2300 Lakeview Road 1900 W. 142nd Street Leawood KS 66224 AR 72202 61 2060 62 2061 700 E. 9th Street #1) 6000 Paris Rd Little Rock Columbia MO 65202 De Nux Distributors 701 N. Broadway Street North Little Rock AR 72114 63 2062 64 65 A B D 1 Product_Cod 2 2001 3 2002 Product_Sale_Price 95 120 4 2003 Product_Descriptior Pale Ale Imperial IPA IPA Imperial Stout Stout Wheat 100 5 2004 105 6 2005 85 90 7 2006 8 9 10 9 Required: 1. Which state was the customer from on Sales Order 20001? O Minnesota (MN) O Arkansas (AR) O Kansas (KS) O Missouri (MO) 2. How many Sales Orders have customers from Missouri on them? O 24 O 16 135 O 20 3. What is the total quantity of products sold to customers from Missouri? O 24 020 O 16 O 135 4. When creating the VLOOKUP for this alt-lab, which variable has the matching key between the Sales_Transactions and Customer tables? O Product_ID O Product_Description O Customer_ID O Sales_Order_ID 5. If you replaced the number 5 for the column_num with a 6 (the third argument in the VLOOKUP function), what descriptive information would return instead of Customer_St? Customer_ID Customer_Zip Sales_Order_ID Sales_Employee_ID NOTE: Throughout this lab, every time a screenshot is requested, use your computer's screenshot tool, and paste each screenshot to the same Word document. Label each screenshot in accordance to what is noted in the lab. This document with all of the screenshots included should be uploaded through Connect as a Word or PDF document when you have reached the final step of the lab. In this lab, you will: Required: 1. Create a column titled Customer_St in the Sales_Transactions table. Use VLOOKUP to match the Customer_ID from the Sales_Transaction table to the Customer_ID in the Customers table, and pull in the data from the column containing Customer_St. 2. Create a column titled Customer_City. Use VLOOKUP to match the Customer_ID from the Sales_Transactions table to the Customer_ID in the Customers table, and pull in the data from the column containing Customer_St in the Sales_Transactions. For a challenge, create both VLOOKUPs without using your mouse, just use your keyboard to reference column and table names! 3. Return to the PivotTable you created, and click Refresh from the Analyze tab on the ribbon to view the two new attributes in the field list, Customer_St and Customer_City. Ask the Question: How can we pull in descriptive attributes from separate tables into the transaction table that we wish to use for analysis? Master the Data: Continue working with the same data file (Lab 4-3 Data). Add in two new columns, one for Customer_St another for Customer_City. Software needed Excel Screen capture tool (Windows: Snipping Tool; Mac: Cmd+Shift+4) Data: Excel File Lab 4-3 Data.xlsx. Perform the Analysis: Refer to Lab 4-3 Alternate in the text for instructions and Lab 4-3 steps for each the of lab parts. Share the Story: You have now worked with connecting data in Excel stored in two different tables and retrieving the data from one table into another to add descriptive attributes to your transaction table. Part 1: Upload Your Files Part 2: Assessment Sales_Order_Quantity_Sold 12 8 5 6 5 2 2 o 8 11 Product_Sale_Price 105 85 105 120 95 95 105 105 105 100 105 100 100 100 95 85 3 7 3 1 8 7 9 85 90 120 Sales_Order_ID 20062 20168 20383 20504 20564 20140 20310 2010 2030R 20308 20009 LUIS 20233 20163 20224 LUCAS 20199 20199 20291 20451 20451 20012 004 20494 20494 10 20537 20250 20251 20003 20241 20253 20005 20005 20090 20033 20055 20211 20211 20338 20395 20393 20097 20418 TO 20543 20277 LUET 20406 Sales_Order_Date Sales_Employee_ID 11/22/19 1007 12/27/19 1007 3/4/20 1007 4/28/20 1007 12/20/19 1006 2/11/20 1007 2/11/20 1006 2/1100 2/11/201006 41 11/2/19 1006 11/21 2006 1/16/20 1006 7107201000 12/25/19 1006 1/13/20 1006 we de 1/7/20 1007 2/4/20 1007 3/24/20 1006 3/24/20 1006 11/4/19 1006 4/8/20 1006 4/8/20 1006 4/19/20 1007 ver 1/22/20 1006 way 1/22/20 1007 Moc 11/1/19 1006 ..... 1/19/20 1007 1/22/20 1007 1007 11/1/19 1006 2011 11/1/19 1006 12/1/19 1006 11/12/19 1007 1/10/20 1007 1/10/20 1007 2/22/20 1006 3/7/20 1007 12/4/19 1007 3/14/20 1006 4/20/20 1006 4720 1/29/20 1006 3/10/20 1007 1000 12/25/19 1007 1/14/20 1006 3/3/20 1006 11/21/19 1006 11/29/19 1007 Customer_ID 2001 2001 2001 2001 2002 2004 2002 2004 2004 2005 2009 suo 2006 2007 2007 2008 2000 2008 . 2008 we 2008 2009 2009 2009 2009 2009 2010 2014 2010 . 2011 2011 2011 2012 2012 2012 2013 2015 2013 2015 2013 2015 2013 2013 2014 2016 2 1 6 2 6 Product_Code 2004 2005 2004 2009 2002 2001 2001 2004 2009 2004 2003 2004 2003 2003 2009 2001 2005 200 2005 2006 2002 2005 200 2005 2004 2004 2005 2004 2006 2004 2005 2006 2001 2002 2003 2005 2006 2006 2005 2005 2005 2003 2005 2003 2006 2003 2006 2000 2005 200 2006 zoo 2001 2001 2003 2001 85 85 105 10 105 85 105 5 11 8 2 10 90 . 105 85 9 90 8 10 1 1 12 12 95 120 100 90 an 30 85 85 85 85 100 100 100 90 90 24 100 6 9 2016 2016 2017 9 12 12 3 1 1 a 4 12 90 2017 2019 20161 9 20229 20379 20056 20080 12 85 90 30 95 95 100 2019 2020 2021 2021 8 11 95 3 4 6 2005 2001 2004 2003 2002 2001 2002 4 4 85 95 105 100 120 95 120 105 105 100 1 6 2004 5 2004 4 1 1 2003 2004 105 100 20305 20305 20305 20312 20031 20223 20230 20266 20452 www 20454 201454 20265 20404 20404 20471 20513 20218 20910 20307 20307 20227 20438 20375 20465 20120 20496 2003 2006 2003 2001 1 1 3 2 6 10 90 100 100 95 2004 4 4 2/10/20 1007 2/10/20 1007 2/10/20 1007 2/12/20 1006 11/12/19 1006 1/13/20 1006 1/15/20 1006 1/27/20 1006 3/25/20 1007 3/25/20 1006 1/27/20 1006 3/9/20 1006 3/9/20 1006 3/29/20 1007 4/13/20 1007 10.1002 1/12/20 1007 24/ 1007 2/11/20 1007 tana 2/11/20 1007 1/14/20 1006 3/18/20 1007 3/2/20 1007 3/28/20 1006 12/12/19 1006 4/8/20 1006 11/7/19 1006 11/8/19 1007 11/22/19 1006 1/1/20 1006 1/1/20 1006 1/24/20 1006 11/20/19 1006 11/30/19 1006 9 9 2 4 20 2004 2005 2001 2006 2006 2004 2005 2004 2003 2004 11 2021 2021 2021 2021 2022 2022 2022 2022 2022 2022 2023 2024 2024 2025 2025 2026 3027 2027 2027 2028 2028 2029 2029 2031 2031 2032 2033 2034 2034 2034 2034 2035 2035 2035 2036 2036 2036 2037 2037 2037 2037 2037 2038 2038 2038 8 10 5 105 105 85 95 90 90 105 85 105 100 105 100 120 95 105 20020 6 11 20024 2003 2002 2001 4 10 20063 20183 20183 20256 20053 US 20086 7 5 2004 105 2 11 85 20202 1/8/20 1006 85 7 7 6 3 3 105 20179 20453 200C 20495 an 20040 2000 20098 4 2 90 05 85 100 2004 2005 2005 2004 2006 2005 2003 2003 2006 2006 2003 2003 2002 2005 12/30/19 1006 3/25/20 1007 4/8/20 1007 11/14/19 1007 1007 12/4/19 1007 1/31/20 1007 2/17/20 1007 3/4/20 1007 11/4/19 1007 11/4/19 1007 12/1/19 1007 4 4 20283 20328 11 10 20384 100 90 90 100 100 120 85 2 20013 20013 20089 4 10 11 3 2039 2039 2039 2040 2010 2010 11 4 7 7. 7 2040 105 95 120 10 90 90 95 95 105 102 100 90 105 105 2 4 9 11 1 2010 2010 2040 2041 2042 2042 2043 2043 2043 2043 2043 2043 2043 2044 2044 2045 12 12 10 95 85 . 100 105 10 2 1 9 9 12 8 95 105 105 100 100 105 2045 6 20055 20440 20516 20516 20290 20280 20280 20378 2010S 20403 20416 20138 20142 20559 0 20017 1 20043 camer 2 20200 3 20200 4 20457 2016 5 20461 SOCAR 6 20545 7 20407 8 20407 9 20357 0 20535 1 20314 2 20450 3 20368 4 20106 5 20126 620126 7 20217 8 20151 9 20518 0 20282 1 20285 2 20424 3 20460 4 20262 5 20023 620068 7 20249 820539 9 20037 20037 1 20001 2 20137 11/20/19 1007 3/19/20 1007 4/13/20 1006 1/30/20 1006 1/30/20 1006 3/3/20 1007 3/9/20 1006 3/9/20 1006 3/13/20 1006 190 12/19/19 1007 21200 12/20/19 1007 4/25/20 1007 ver 11/7/19 1006 11/16/19 1006 wedd 1/7/20 1007 . 1/7/20 1007 Jacinto 3/26/20 1006 2/27/201007 3/27/20 1007 1007 4/21/20 1007 3/10/20 1006 3/10/20 1006 2/27/20 1006 4/19/20 1007 14100 2/13/20 1007 . 3/24/20 1007 2/29/20 1007 12/7/19 1006 12/16/19 1006 12/16/19 1006 1/12/20 1007 12/22/19 1007 4/14/20 1007 1/31/20 1006 vo 2/1/20 1007 3/16/20 1006 3/27/20 1006 1/26/20 1007 11/8/19 1007 11/24/19 1007 1/22/20 1006 4/19/20 1006 11/13/19 1007 11/13/19 1007 11/1/19 1006 12/18/19 1006 2004 2001 2002 2006 2001 2001 2004 2003 2006 2004 2004 2001 2005 2003 Zoo 2004 2002 2001 2004 5002 2003 2004 2006 2005 2003 2004 2003 2003 2004 2004 Kons 2005 2003 2004 2001 2002 2003 2004 2003 2005 2005 2004 2004 2004 2004 2005 2001 2003 2 90 85 os 100 100 105 100 100 105 9 11 1 6 105 85 9 9 11 8 2046 2046 2047 2048 2048 2048 2048 2049 20 2049 2050 2050 2050 2050 2051 2052 2052 2053 8 7 2 10 6 12 100 105 95 120 100 105 100 85 or 85 2 9 105 105 105 8 2053 105 2054 10 3 3 3 105 85 2054 2056 2056 10 95 10 100 133 20155 F2056 2003 100 2056 2003 134 20439 135 20261 136 20492 8 1 6 100 2057 2003 100 2057 2003 10 100 137 20093 2058 2002 1 120 138 20325 2058 2003 7 100 139 20124 2059 2004 11 105 2060 2005 6 85 140 20059 141 20103 2060 2003 7 100 12/23/19 1006 3/19/20 1007 1/25/20 1006 4/8/20 1006 12/2/19 1007 2/15/20 1006 12/15/19 1007 11/22/19 1006 12/5/19 1006 1/28/20 1007 4/7/20 1006 4/17/20 1007 1/6/20 1007 1/19/20 1006 3/26/20 1007 3/19/20 1007 4/10/20 1006 4/10/20 1006 142 20271 2060 2005 11 85 143 20491 2060 2003 3 100 144 20532 2060 2003 11 100 145 20192 2061 2004 12 105 146 20242 2061 2004 3 105 147 20458 2061 2003 11 100 148 20441 2062 2003 10 100 149 20503 2062 2005 6 85 2062 2004 2 105 150 20503 151 152 153 1 Customer_IC Customer_s Customer_zi 2 2001 IA 50320 3 2002 TX 75226 Business_Name Customer_Addres Customer_City Beverage Distributors 3221 SE 14th Street Des Moines Deep Ellum Brewing Company 2823 St Louis Street Dallas Schatz Distributing Co. 3140 S. 28th Steet Kansas City Arkansas Craft Distributors 1515 E. 4th Street Little Rock Fleck Sales Co. 11125 High Life Ct SW Cedar Rapids Wholesale Liquor 4720 S Broadway St Wichita 4 2003 KS 66106 5 2004 6 2005 7 2006 AR IA 72202 52404 67216 KS 8 2007 TX 76111 9 2008 NE 68117 10 2009 TX 75207 11 2010 MO 65201 12 2011 13 2012 14 2013 MO TN MO 64108 38134 65201 15 2014 LA 70125 38118 16 2015 TN 17 2016 LA 70121 Texas Wholesale 550 North Beach Street Fort Worth D&J Beverage Services 4740 G. Street Omaha Andrews Distributing 2730 Distributing Company Company Dallas 111 South 9th Street Craft Beer Cellar Suite #10 Columbia 1903 Woodland United Beverage Co. Avenue Kansas City Star Distributors 16290 Shelby View Dr. Memphis Golden Barrel Inc 1501 1-70 Complex Ct Columbia Louisiana Specialty Drinks 1603 S Gayoso St New Orleans AS Barboro Inc 5020 Tuggle Rd Memphis Republic National Distributing Company 809 Jefferson Hwy New Orleans MBA Tulsa Beverage Co 510 W. Skelly Drive Tulsa Duff Beer 1319 S. Houston Road Pasadena Faust Distributing Company 10040 East Freeway Houston Glazer's 6201 Stilwell Street Kansas City LDF Sales & 10718 E. Marshall Distributing Street Tulsa Wil Fischer Companies 3539 W Farm Rd 142 Springfield Quality Beverage Company 7029 E. Reading Place Tulsa Brown Distributing 8711 Johnny Morris Inc Road Austin Boardwalk 4157 S. 72nd East Distribution Avenue Tulsa Texas Quality Beverage 13300 Park Vista Blvd Fort Worth lowa Beverage Systems 2115 NE 58th Avenue Des Moines 18 2017 OK 74107 19 2018 TX 77502 TX 20 2019 21 2020 77029 64120 MO 22 2021 OK 74116 23 2022 MO 65807 24 2023 OK 74115 25 2024 TX 78724 26 2025 OK 74145 27 2026 TX 76177 28 2027 IA 50313 29 2028 OK 73118 30 2029 KS 66609 MO 31 2030 32 2031 65807 52240 IA 33 2032 TX 72220 34 2033 AR 72901 35 2034 NE 68138 36 2035 KS 66219 37 2036 NE 68138 38 2037 NE 68127 39 2038 OK 73107 40 2039 TX 75235 Beer Distributors of 6301 Wateford Blvd Oklahoma #105 Oklahoma City 4235 SW Burlingame Strathman Sales Co. Road Topeka Heart of America Beverage 2860 S Austin Ave Springfield Evans Distributing 3750 Liberty Drive lowa City Texas Specialty Beverage 2512 Program Drive Dallas Belle Point Beverages Inc. 1 Belle Point Place Fort Smith Quality Brands of Omaha 13255 Centech Road Omaha Wil Fischer Distributing of Kansas 17501 W 98th Street Lenexa Premier Midwest Beverage 10367 S. 134th Street Omaha Johnson Brothers of Nebraska 9320 Street Omaha 421 North Portland Capital Distributing Avenue Oklahoma City Ben E. Keith 1805 Record Crossing Company Road Dallas Burford Distributing 925 H Street South Fort Smith Standard Beverage Corporation 2416 E 37th St N Wichita Arkansas Craft Distributors 1605 W. Shady Grove Springdale 7777 Washington Silver Eagle Avenue Houston Grand Wholesale Liquor Inc 609 S Grand St. Amarillo Premium Brands of NW Arkansas 1032 S. Razorback Road Fayetteville Ben E. Keith Company 2300 N Lakeside Dr Amarillo City Wholesale Liquor Co 4340 Washington Ave New Orleans Reed Beverage Distributor 3333 SE 3rd Ave Amarillo Doll Distributing Inc. 3501 23rd Avenue Council Bluffs Broadway Beer 2304 North Broadway Distributing Co Avenue Oklahoma City Capitol Wright 3834 Promontory South Austin Point Drive Austin 41 2040 AR 72901 42 2041 KS 67219 43 2042 AR 72764 44 2043 TX 77007 45 2044 TX 79104 46 2045 AR 72701 47 2046 TX 79108 48 2047 LA 70125 49 2048 TX 79104 50 2049 IA 51501 51 2050 OK 73103 52 2051 TX 78744 53 2052 Sooner Beer Co 605 North Tulsa Avenue Oklahoma City OK 73107 53 2052 OK 605 North Tulsa Avenue Oklahoma City 3002 Chapel Hill Road Stillwater 73107 74074 54 2053 OK 3300 Pleasant Valley Lane 55 2054 Arlington TX 76015 56 2055 203 E. 11th Avenue North Kansas City MO 64116 Sooner Beer CO Barn Spirits American Wholesale Distribution North Kansas City Beverage Co. General Distributors BDT Beverage Standard Beverage Corporation High Life Sales Co. Wholesale Beer Distributors Nauser Beverage KS 67211 57 2056 58 2057 800 E Indianapolis St. Wichita 2115 Spicer Cove #113 Memphis TN 38134 Lawrence KS 66049 59 2058 60 2059 2300 Lakeview Road 1900 W. 142nd Street Leawood KS 66224 AR 72202 61 2060 62 2061 700 E. 9th Street #1) 6000 Paris Rd Little Rock Columbia MO 65202 De Nux Distributors 701 N. Broadway Street North Little Rock AR 72114 63 2062 64 65 A B D 1 Product_Cod 2 2001 3 2002 Product_Sale_Price 95 120 4 2003 Product_Descriptior Pale Ale Imperial IPA IPA Imperial Stout Stout Wheat 100 5 2004 105 6 2005 85 90 7 2006 8 9 10 9 Required: 1. Which state was the customer from on Sales Order 20001? O Minnesota (MN) O Arkansas (AR) O Kansas (KS) O Missouri (MO) 2. How many Sales Orders have customers from Missouri on them? O 24 O 16 135 O 20 3. What is the total quantity of products sold to customers from Missouri? O 24 020 O 16 O 135 4. When creating the VLOOKUP for this alt-lab, which variable has the matching key between the Sales_Transactions and Customer tables? O Product_ID O Product_Description O Customer_ID O Sales_Order_ID 5. If you replaced the number 5 for the column_num with a 6 (the third argument in the VLOOKUP function), what descriptive information would return instead of Customer_St? Customer_ID Customer_Zip Sales_Order_ID Sales_Employee_ID
