Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Use the XLOOKUP function in the Price column of the Orders worksheet to return the price based on the Product column in the Orders worksheet.

Use the XLOOKUP function in the Price column of the Orders worksheet to return the price based on the Product column in the Orders worksheet. Use the PriceTable in the Prices worksheet. You MUST use structured references to the PriceTable fields in your XLOOKUP function. You may NOT add to, delete, or modify any of the Product data in the Orders worksheet. Additionally, you may NOT add any additional columns to the data. THE DATA OF PRICES AND PRODUCT ARE on a protected worksheet that can not be unlocked

image text in transcribedimage text in transcribed \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|} \hline & A & B & C & D & E & F & G & H & I & j \\ \hline & Order ID & Salesperson & Order Date & Ship To & State Name & Region & Product & Quantity & Price & Discount \% \\ \hline & MG1000 & Michelle Gri & 1/1/2021 & 1 9-UT-28 & Utah & West & Texans Jersey & 1 & & 0% \\ \hline & SP1000 & Susan Peter: & 1/1/2021 & 14WY85 & Wyoming & West & Buccaneers Jersey & 2 & & 0% \\ \hline & SP1001 & Susan Peter: & : 1/1/2021 & 1 2-ID-83 & Idaho & West & 49ers Jersey & 8 & & 10% \\ \hline & TJ1000 & Timothy Jam & 1/1/2021 & 12 -IL-24 & Illinois & Central & Dolphins Jersey & 8 & & 10% \\ \hline & TJ1001 & Timothy Jam & 1/1/2021 & 1 1-CO-77 & Colorado & West & Chiefs Jersey & 1 & & 0% \\ \hline & TJ1001 & Timothy Jarr & 1/1/2021 & 1 1-CO-77 & Colorado & West & Broncos Jersey & 2 & & 0% \\ \hline & MG1001 & 1 Michelle Gr: & 1/2/2021 & 1 9-TN-76 & Tennessee & East & Panthers Jersey & 1 & & 0% \\ \hline & SP1002 & Susan Peter: & 1/2/2021 & 12KS90 & Kansas & Central & Broncos Jersey & 3 & & 0% \\ \hline & SP1003 & Susan Peter: & 1/2/2021 & 15MT60 & Montana & West & Packers Jersey & 8 & & 10% \\ \hline 1 & TJ1002 & Timothy Jarr & 1/2/2021 & 1 4-ID-45 & Idaho & West & Packers Jersey & 2 & & 0% \\ \hline 2 & MG1002 & 2 Michelle Gri & 1/3/2021 & 1 1-VA-83 & Virginia & East & Falcons Jersey & 7 & & 10% \\ \hline 3 & SP1004 & Susan Peter: & 1/3/2021 & 19CA15 & California & West & Rams Jersey & 6 & & 10% \\ \hline 4 & SP1004 & Susan Peter: & 1/3/2021 & 19CA15 & California & West & Jaguars Jersey & 1 & & 0% \\ \hline 5 & SP1004 & Susan Peter: & 1/3/2021 & 19CA15 & California & West & Texans Jersey & 2 & & 0% \\ \hline 5 & SP1004 & Susan Peter: & 1/3/2021 & 19CA15 & California & West & Dolphins Jersey & 1 & & 0% \\ \hline 7 & SP1004 & Susan Peter: & 1/3/2021 & 19CA15 & California & West & Colts Jersey & 3 & & 5% \\ \hline 8 & |TJ1003 & Timothy Jam & 1/3/2021 & 16AR37 & Arkansas & South & Dolphins Jersey & 5 & & 10% \\ \hline 9 & MG1003 & Michelle Gri & 1/4/2021 & 1TX77 & Texas & South & Chiefs Jersey & 6 & & 10% \\ \hline & MG1004 & 4 Michelle Gr: & 1/4/2021 & 1 2-AK-77 & Alaska & West & Falcons Jersey & 5 & & 10% \\ \hline & TJ1004 & Timothy Jarr & 1/4/2021 & 1 4-AK-24 & Alaska & West & Packers Jersey & 3 & & 5% \\ \hline 2 & MG1005 & 5 Michelle Gri & 1/5/2021 & 1 7-AK-65 & Alaska & West & Rams Jersey & 1 & & 0% \\ \hline & TJ1005 & Timothy Jarr & 1/5/2021 & 4-AL-13 & Alabama & South & Patriots Jersey & 1 & & 0% \\ \hline 4 & TJ1005 & Timothy Jarr & 1/5/2021 & 14AL13 & Alabama & South & Broncos Jersey & 2 & & 0% \\ \hline 5 & TJ1005 & Timothy Jam & 1/5/2021 & 4-AL-13 & Alabama & South & Seahawks Jersey & 5 & & 10% \\ \hline & TJ1005 & Timothy Jarr & 1/5/2021 & 1 4-AL-13 & Alabama & South & Steelers Jersey & 1 & & 0% \\ \hline & TJ1005 & Timothy Jam & 1/5/2021 & 4-AL-13 & Alabama & South & Rams Jersey & 10 & & 20% \\ \hline & MG1006 & 5 Michelle Gr: & 1/6/2021 & 1 8-MT-66 & Montana & West & Chargers Jersey & 1 & & 0% \\ \hline & MG1007 & 7 Michelle Gri & 1/7/2021 & 17CA83 & California & West & Saints Jersey & 1 & & 0% \\ \hline & SP1005 & Susan Peter: & : 1/7/2021 & 19-NE-10 & Nebraska & Central & Chiefs Jersey & 4 & & 5% \\ \hline & MG1008 & 3 Michelle Gr: & 1/8/2021 & 18 OR-65 & Oregon & West & Panthers Jersey & 3 & & 5% \\ \hline & MG1008 & 3 Michelle Gr: & 1/8/2021 & 18 OR-65 & Oregon & West & Patriots Jersey & 7 & & 10% \\ \hline 8 & MG1008 & Michelle Gri & 1/8/2021 & 18OR65 & Oregon & West & Titans Jersey & 9 & & 10% \\ \hline & MG1008 & 3 Michelle Gri & 1/8/2021 & 18 OR-65 & Oregon & West & Vikings Jersey & 4 & & 5% \\ \hline & MG1008 & Michelle Gri & 1/8/2021 & 18OR65 & Oregon & West & Texans Jersey & 4 & & 5% \\ \hline & SP1006 & Susan Peter: & 1/10/2021 & 1 8-KS-78 & Kansas & Central & Chiefs Jersey & 2 & & 0% \\ \hline 7 & SP1006 & Susan Peter: & 1/10/2021 & 1 8-KS-78 & Kansas & Central & Eagles Jersey & 7 & & 10% \\ \hline 8 & SP1006 & Susan Peter: & 1/10/2021 & 1 8-KS-78 & Kansas & Central & Browns Jersey & 7 & & 10% \\ \hline 9 & TJ1006 & Timothy Jarr & 1/12/2021 & 15 WA-40 & Washington & West & Patriots Jersey & 7 & & 10% \\ \hline & SP1007 & Susan Peter: & : 1/14/2021 & 15WY64 & Wyoming & West & Chargers Jersey & 4 & & 5% \\ \hline 1 & TJ1007 & Timothy Jam & 1/14/2021 & 2-NY-91 & New York & Northeast & Cowboys Jersey & 8 & & 10% \\ \hline 2 & TJ1007 & Timothy Jarr & 1/14/2021 & 12-NY-91 & New York & Northeast & Chiefs Jersey & 7 & & 10% \\ \hline 3 & MG1009 & Michelle Gri & 1/16/2021 & 13MT63 & Montana & West & Steelers Jersey & 2 & & 0% \\ \hline 4 & TJ1008 & Timothy Jarr & 1/16/2021 & 1 1-CO-46 & Colorado & West & Bills Jersey & 8 & & 10% \\ \hline 5 & MG1010 & Michelle Gr: & 1/17/2021 & 1 3-OR-37 & Oregon & West & Rams Jersey & 10 & & 20% \\ \hline 5 & SP1008 & Susan Peter: & 1/17/2021 & 16-TX-85 & Texas & South & Jets Jersey & 2 & & 0% \\ \hline & TJ1009 & Timothy Jarr & 1/17/2021 & 12-CO-62 & Colorado & West & Lions Jersey & 10 & & 20% \\ \hline & MG1011 & 1 Michelle Gr: & 1/18/2021 & 16NE72 & Nebraska & Central & Bills Jersey & 8 & & 10% \\ \hline 9 & TJ1010 & Timothy Jam & 1/18/2021 & 6-TX-63 & Texas & South & Lions Jersey & 10 & & 20% \\ \hline & TJ1010 & Timothy Jarr & 1/18/2021 & 6-TX-63 & Texas & South & Patriots Jersey & 1 & & 0% \\ \hline & TJ1010 & Timothy Jarr & 1/18/2021 & 16TX63 & Texas & South & Ravens Jersey & 2 & & 0% \\ \hline & TJ1011 & Timothy Jarr & 1/20/2021 & 17CO89 & Colorado & West & Steelers Jersey & 6 & & 10% \\ \hline & AG1000 & Adam Gross & 1/24/2021 & 1 1-TX-46 & Texas & South & Broncos Jersey & 1 & & 0% \\ \hline & AG1001 & Adam Gross & 1/24/2021 & 3-SD-59 & South Dakota & Central & Rams Jersey & 2 & & 0% \\ \hline & AG1002 & Adam Gross & 1/24/2021 & 19KS56 & Kansas & Central & Eagles Jersey & 2 & & 0% \\ \hline \end{tabular} \begin{tabular}{|c|c|c|} \hline & A & B \\ \hline & Price & Product \\ \hline$ & 99.95 & 49ers Jersey \\ \hline$ & 68.97 & Bears Jersey \\ \hline$ & 90.99 & Bengals Jersey \\ \hline$ & 98.27 & Bills Jersey \\ \hline$ & 89.99 & Broncos Jersey \\ \hline$ & 73.00 & Browns Jersey \\ \hline$ & 79.99 & Buccaneers Jersey \\ \hline$ & 75.98 & Cardinals Jersey \\ \hline$ & 89.25 & Chargers Jersey \\ \hline$ & 99.95 & Chiefs Jersey \\ \hline$ & 70.99 & Colts Jersey \\ \hline$ & 92.49 & Cowboys Jersey \\ \hline$ & 85.50 & Dolphins Jersey \\ \hline$ & 76.99 & Eagles Jersey \\ \hline$ & 85.99 & Falcons Jersey \\ \hline$ & 77.50 & Giants Jersey \\ \hline$ & 61.89 & Jaguars Jersey \\ \hline$ & 82.50 & Jets Jersey \\ \hline$ & 88.88 & Lions Jersey \\ \hline$ & 80.99 & Packers Jersey \\ \hline$ & 62.89 & Panthers Jersey \\ \hline$ & 92.99 & Patriots Jersey \\ \hline$ & 72.99 & Raiders Jersey \\ \hline$ & 60.99 & Rams Jersey \\ \hline$ & 72.47 & Ravens Jersey \\ \hline$ & 95.99 & Saints Jersey \\ \hline$ & 75.95 & Seahawks Jersey \\ \hline$ & 84.95 & Steelers Jersey \\ \hline$ & 84.52 & Texans Jersey \\ \hline$ & 64.99 & Titans Jersey \\ \hline$ & 82.88 & Vikings Jersey \\ \hline$ & 73.99 & Washington Football Team Jersey \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|} \hline & A & B & C & D & E & F & G & H & I & j \\ \hline & Order ID & Salesperson & Order Date & Ship To & State Name & Region & Product & Quantity & Price & Discount \% \\ \hline & MG1000 & Michelle Gri & 1/1/2021 & 1 9-UT-28 & Utah & West & Texans Jersey & 1 & & 0% \\ \hline & SP1000 & Susan Peter: & 1/1/2021 & 14WY85 & Wyoming & West & Buccaneers Jersey & 2 & & 0% \\ \hline & SP1001 & Susan Peter: & : 1/1/2021 & 1 2-ID-83 & Idaho & West & 49ers Jersey & 8 & & 10% \\ \hline & TJ1000 & Timothy Jam & 1/1/2021 & 12 -IL-24 & Illinois & Central & Dolphins Jersey & 8 & & 10% \\ \hline & TJ1001 & Timothy Jam & 1/1/2021 & 1 1-CO-77 & Colorado & West & Chiefs Jersey & 1 & & 0% \\ \hline & TJ1001 & Timothy Jarr & 1/1/2021 & 1 1-CO-77 & Colorado & West & Broncos Jersey & 2 & & 0% \\ \hline & MG1001 & 1 Michelle Gr: & 1/2/2021 & 1 9-TN-76 & Tennessee & East & Panthers Jersey & 1 & & 0% \\ \hline & SP1002 & Susan Peter: & 1/2/2021 & 12KS90 & Kansas & Central & Broncos Jersey & 3 & & 0% \\ \hline & SP1003 & Susan Peter: & 1/2/2021 & 15MT60 & Montana & West & Packers Jersey & 8 & & 10% \\ \hline 1 & TJ1002 & Timothy Jarr & 1/2/2021 & 1 4-ID-45 & Idaho & West & Packers Jersey & 2 & & 0% \\ \hline 2 & MG1002 & 2 Michelle Gri & 1/3/2021 & 1 1-VA-83 & Virginia & East & Falcons Jersey & 7 & & 10% \\ \hline 3 & SP1004 & Susan Peter: & 1/3/2021 & 19CA15 & California & West & Rams Jersey & 6 & & 10% \\ \hline 4 & SP1004 & Susan Peter: & 1/3/2021 & 19CA15 & California & West & Jaguars Jersey & 1 & & 0% \\ \hline 5 & SP1004 & Susan Peter: & 1/3/2021 & 19CA15 & California & West & Texans Jersey & 2 & & 0% \\ \hline 5 & SP1004 & Susan Peter: & 1/3/2021 & 19CA15 & California & West & Dolphins Jersey & 1 & & 0% \\ \hline 7 & SP1004 & Susan Peter: & 1/3/2021 & 19CA15 & California & West & Colts Jersey & 3 & & 5% \\ \hline 8 & |TJ1003 & Timothy Jam & 1/3/2021 & 16AR37 & Arkansas & South & Dolphins Jersey & 5 & & 10% \\ \hline 9 & MG1003 & Michelle Gri & 1/4/2021 & 1TX77 & Texas & South & Chiefs Jersey & 6 & & 10% \\ \hline & MG1004 & 4 Michelle Gr: & 1/4/2021 & 1 2-AK-77 & Alaska & West & Falcons Jersey & 5 & & 10% \\ \hline & TJ1004 & Timothy Jarr & 1/4/2021 & 1 4-AK-24 & Alaska & West & Packers Jersey & 3 & & 5% \\ \hline 2 & MG1005 & 5 Michelle Gri & 1/5/2021 & 1 7-AK-65 & Alaska & West & Rams Jersey & 1 & & 0% \\ \hline & TJ1005 & Timothy Jarr & 1/5/2021 & 4-AL-13 & Alabama & South & Patriots Jersey & 1 & & 0% \\ \hline 4 & TJ1005 & Timothy Jarr & 1/5/2021 & 14AL13 & Alabama & South & Broncos Jersey & 2 & & 0% \\ \hline 5 & TJ1005 & Timothy Jam & 1/5/2021 & 4-AL-13 & Alabama & South & Seahawks Jersey & 5 & & 10% \\ \hline & TJ1005 & Timothy Jarr & 1/5/2021 & 1 4-AL-13 & Alabama & South & Steelers Jersey & 1 & & 0% \\ \hline & TJ1005 & Timothy Jam & 1/5/2021 & 4-AL-13 & Alabama & South & Rams Jersey & 10 & & 20% \\ \hline & MG1006 & 5 Michelle Gr: & 1/6/2021 & 1 8-MT-66 & Montana & West & Chargers Jersey & 1 & & 0% \\ \hline & MG1007 & 7 Michelle Gri & 1/7/2021 & 17CA83 & California & West & Saints Jersey & 1 & & 0% \\ \hline & SP1005 & Susan Peter: & : 1/7/2021 & 19-NE-10 & Nebraska & Central & Chiefs Jersey & 4 & & 5% \\ \hline & MG1008 & 3 Michelle Gr: & 1/8/2021 & 18 OR-65 & Oregon & West & Panthers Jersey & 3 & & 5% \\ \hline & MG1008 & 3 Michelle Gr: & 1/8/2021 & 18 OR-65 & Oregon & West & Patriots Jersey & 7 & & 10% \\ \hline 8 & MG1008 & Michelle Gri & 1/8/2021 & 18OR65 & Oregon & West & Titans Jersey & 9 & & 10% \\ \hline & MG1008 & 3 Michelle Gri & 1/8/2021 & 18 OR-65 & Oregon & West & Vikings Jersey & 4 & & 5% \\ \hline & MG1008 & Michelle Gri & 1/8/2021 & 18OR65 & Oregon & West & Texans Jersey & 4 & & 5% \\ \hline & SP1006 & Susan Peter: & 1/10/2021 & 1 8-KS-78 & Kansas & Central & Chiefs Jersey & 2 & & 0% \\ \hline 7 & SP1006 & Susan Peter: & 1/10/2021 & 1 8-KS-78 & Kansas & Central & Eagles Jersey & 7 & & 10% \\ \hline 8 & SP1006 & Susan Peter: & 1/10/2021 & 1 8-KS-78 & Kansas & Central & Browns Jersey & 7 & & 10% \\ \hline 9 & TJ1006 & Timothy Jarr & 1/12/2021 & 15 WA-40 & Washington & West & Patriots Jersey & 7 & & 10% \\ \hline & SP1007 & Susan Peter: & : 1/14/2021 & 15WY64 & Wyoming & West & Chargers Jersey & 4 & & 5% \\ \hline 1 & TJ1007 & Timothy Jam & 1/14/2021 & 2-NY-91 & New York & Northeast & Cowboys Jersey & 8 & & 10% \\ \hline 2 & TJ1007 & Timothy Jarr & 1/14/2021 & 12-NY-91 & New York & Northeast & Chiefs Jersey & 7 & & 10% \\ \hline 3 & MG1009 & Michelle Gri & 1/16/2021 & 13MT63 & Montana & West & Steelers Jersey & 2 & & 0% \\ \hline 4 & TJ1008 & Timothy Jarr & 1/16/2021 & 1 1-CO-46 & Colorado & West & Bills Jersey & 8 & & 10% \\ \hline 5 & MG1010 & Michelle Gr: & 1/17/2021 & 1 3-OR-37 & Oregon & West & Rams Jersey & 10 & & 20% \\ \hline 5 & SP1008 & Susan Peter: & 1/17/2021 & 16-TX-85 & Texas & South & Jets Jersey & 2 & & 0% \\ \hline & TJ1009 & Timothy Jarr & 1/17/2021 & 12-CO-62 & Colorado & West & Lions Jersey & 10 & & 20% \\ \hline & MG1011 & 1 Michelle Gr: & 1/18/2021 & 16NE72 & Nebraska & Central & Bills Jersey & 8 & & 10% \\ \hline 9 & TJ1010 & Timothy Jam & 1/18/2021 & 6-TX-63 & Texas & South & Lions Jersey & 10 & & 20% \\ \hline & TJ1010 & Timothy Jarr & 1/18/2021 & 6-TX-63 & Texas & South & Patriots Jersey & 1 & & 0% \\ \hline & TJ1010 & Timothy Jarr & 1/18/2021 & 16TX63 & Texas & South & Ravens Jersey & 2 & & 0% \\ \hline & TJ1011 & Timothy Jarr & 1/20/2021 & 17CO89 & Colorado & West & Steelers Jersey & 6 & & 10% \\ \hline & AG1000 & Adam Gross & 1/24/2021 & 1 1-TX-46 & Texas & South & Broncos Jersey & 1 & & 0% \\ \hline & AG1001 & Adam Gross & 1/24/2021 & 3-SD-59 & South Dakota & Central & Rams Jersey & 2 & & 0% \\ \hline & AG1002 & Adam Gross & 1/24/2021 & 19KS56 & Kansas & Central & Eagles Jersey & 2 & & 0% \\ \hline \end{tabular} \begin{tabular}{|c|c|c|} \hline & A & B \\ \hline & Price & Product \\ \hline$ & 99.95 & 49ers Jersey \\ \hline$ & 68.97 & Bears Jersey \\ \hline$ & 90.99 & Bengals Jersey \\ \hline$ & 98.27 & Bills Jersey \\ \hline$ & 89.99 & Broncos Jersey \\ \hline$ & 73.00 & Browns Jersey \\ \hline$ & 79.99 & Buccaneers Jersey \\ \hline$ & 75.98 & Cardinals Jersey \\ \hline$ & 89.25 & Chargers Jersey \\ \hline$ & 99.95 & Chiefs Jersey \\ \hline$ & 70.99 & Colts Jersey \\ \hline$ & 92.49 & Cowboys Jersey \\ \hline$ & 85.50 & Dolphins Jersey \\ \hline$ & 76.99 & Eagles Jersey \\ \hline$ & 85.99 & Falcons Jersey \\ \hline$ & 77.50 & Giants Jersey \\ \hline$ & 61.89 & Jaguars Jersey \\ \hline$ & 82.50 & Jets Jersey \\ \hline$ & 88.88 & Lions Jersey \\ \hline$ & 80.99 & Packers Jersey \\ \hline$ & 62.89 & Panthers Jersey \\ \hline$ & 92.99 & Patriots Jersey \\ \hline$ & 72.99 & Raiders Jersey \\ \hline$ & 60.99 & Rams Jersey \\ \hline$ & 72.47 & Ravens Jersey \\ \hline$ & 95.99 & Saints Jersey \\ \hline$ & 75.95 & Seahawks Jersey \\ \hline$ & 84.95 & Steelers Jersey \\ \hline$ & 84.52 & Texans Jersey \\ \hline$ & 64.99 & Titans Jersey \\ \hline$ & 82.88 & Vikings Jersey \\ \hline$ & 73.99 & Washington Football Team Jersey \\ \hline \end{tabular}

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Financial Accounting

Authors: Robert Libby, Patricia Libby, Daniel Short, George Kanaan, Maureen Sterling

7th Canadian Edition

1260065952, 978-1260065954

More Books

Students also viewed these Accounting questions

Question

16.7 Describe the three steps in the collective bargaining process.

Answered: 1 week ago