In the State column (column C), enter a VLOOKUP() function that will find the state each customer lives in (based on the CustomerID (column B) in the RawData worksheet) using the Customers worksheet, which contains each customer's data (including IDs, names and states). 

In the Sector column (column E), enter a VLOOKUP() function to find the sector for each stock (based on the Ticker symbol (column D) in the RawData worksheet) using the Stocks worksheet, which contains each stock's data (including its ticker symbol, stock name and sector).

Transcribed Image Text:

AutoSave Off File Home Insert Paste X (6 Clipboard Q 11 Draw Page Layout Calibri B IU L... ⠀ X ✓11 Font V fx 3 4 V A A A State CA3_Datafile (1) - Excel Formulas Data Review View Help с == = IEEE BE A B 1 TransactionID + CustomerID 2 TR093016275 CN0556835 3 TR093011962 4 TR093010648 5 TR093016432 6 TR093003876 7 TR093024190 8 TR093021236 9 TR093014330 10 TR093025402 11 TR093022880 12 TR093017116 13 TR093009306 14 TR093004193 15 TR093012693 16 TR093024338 17 TR093014340 18 TR093011272 19 TR093023692 20 TR093013058 21 TR093026660 22 TR093015203 23 TR093015850 24 TR093010217 25 TR093017950 26 TR093010071 27 TR093017225 28 TR093018091 29 TR093021427 CN0424934 CN0113219 CN0153111 CN0550197 CN0602325 CN0429701 CN0573414 CN0470369 CN0528378 CN0627280 CN0251229 CN0011401 CN0570708 CN0455458 CN0706104 CN0571081 CN0037714 CN0691720 CN0432497 CN0614351 CN0027268 CN0659542 CN0306295 CN0373214 CN0292420 CN0479072 CN0088128 Analysis RawData Customers Stocks Sectors States D ▾ Ticker CHK BWA SYMC HAR VMC MSI TSS JBHT NAVI MYL TEL UPS BBY HAL AKAM TSS HOT COST >¶ ✓ BBY ROST CTAS CCI CA TMK DPS CTAS XRX TWC Alignment ab Wrap Text E Sector Qty Merge & Center F 9600 6425 10150 1525 5875 4950 4975 11750 7775 9700 11450 4850 1575 2725 3050 7725 2600 8375 8550 10375 5525 3500 12375 7900 2600 8975 8175 2925 + V 5 Search 92.21 109.88 78.89 40.38 265.11 11.37 45.53 190.13 21.81 52.2 43.99 89.16 62.45 62.26 124.77 138.96 30 67.71 74.26 15.28 26.65 3.65 77.72 31.54 36.42 48.95 26.52 63.64 General $ % 900-000 V Number G H UnitPrice Total Dollar Value Conditional Format as Cell Formatting Table Styles Styles $885,216.00 $705,979.00 $800,733.50 $61,579.50 $1,557,521.25 $56,281.50 $226,511.75 $2,234,027.50 $169,572.75 $506,340.00 $503,685.50 $432,426.00 $98,358.75 $169,658.50 $380,548.50 $1,073,466.00 $78,000.00 $567,071.25 $634,923.00 $158,530.00 $147,241.25 $12,775.00 $961,785.00 $249,166.00 $94,692.00 $439,326.25 $216,801.00 $186,147.00 I J ☐ TIX U Insert Delete Format ←T K Cells L M WE Σ AutoSave Off File Home Insert Paste X (6 Clipboard Q 11 Draw Page Layout Calibri B IU L... ⠀ X ✓11 Font V fx 3 4 V A A A State CA3_Datafile (1) - Excel Formulas Data Review View Help с == = IEEE BE A B 1 TransactionID + CustomerID 2 TR093016275 CN0556835 3 TR093011962 4 TR093010648 5 TR093016432 6 TR093003876 7 TR093024190 8 TR093021236 9 TR093014330 10 TR093025402 11 TR093022880 12 TR093017116 13 TR093009306 14 TR093004193 15 TR093012693 16 TR093024338 17 TR093014340 18 TR093011272 19 TR093023692 20 TR093013058 21 TR093026660 22 TR093015203 23 TR093015850 24 TR093010217 25 TR093017950 26 TR093010071 27 TR093017225 28 TR093018091 29 TR093021427 CN0424934 CN0113219 CN0153111 CN0550197 CN0602325 CN0429701 CN0573414 CN0470369 CN0528378 CN0627280 CN0251229 CN0011401 CN0570708 CN0455458 CN0706104 CN0571081 CN0037714 CN0691720 CN0432497 CN0614351 CN0027268 CN0659542 CN0306295 CN0373214 CN0292420 CN0479072 CN0088128 Analysis RawData Customers Stocks Sectors States D ▾ Ticker CHK BWA SYMC HAR VMC MSI TSS JBHT NAVI MYL TEL UPS BBY HAL AKAM TSS HOT COST >¶ ✓ BBY ROST CTAS CCI CA TMK DPS CTAS XRX TWC Alignment ab Wrap Text E Sector Qty Merge & Center F 9600 6425 10150 1525 5875 4950 4975 11750 7775 9700 11450 4850 1575 2725 3050 7725 2600 8375 8550 10375 5525 3500 12375 7900 2600 8975 8175 2925 + V 5 Search 92.21 109.88 78.89 40.38 265.11 11.37 45.53 190.13 21.81 52.2 43.99 89.16 62.45 62.26 124.77 138.96 30 67.71 74.26 15.28 26.65 3.65 77.72 31.54 36.42 48.95 26.52 63.64 General $ % 900-000 V Number G H UnitPrice Total Dollar Value Conditional Format as Cell Formatting Table Styles Styles $885,216.00 $705,979.00 $800,733.50 $61,579.50 $1,557,521.25 $56,281.50 $226,511.75 $2,234,027.50 $169,572.75 $506,340.00 $503,685.50 $432,426.00 $98,358.75 $169,658.50 $380,548.50 $1,073,466.00 $78,000.00 $567,071.25 $634,923.00 $158,530.00 $147,241.25 $12,775.00 $961,785.00 $249,166.00 $94,692.00 $439,326.25 $216,801.00 $186,147.00 I J ☐ TIX U Insert Delete Format ←T K Cells L M WE Σ

Answer rating: 100% (QA)

S tep 1 Create table with three worksheet As sample 1 Raw data 2 States 3 Sectors On worksheet Rawdata use vlookup on state and sector for state use f... View the full answer