Use INDEX and MATCH functions to fill in a table that extracts the amounts shipp

Question

Use INDEX and MATCH functions to fill in a table that extracts the amounts shipped between each pair of cities in the Excel file General Appliance Corporation. Your table should display as follows, and the formula for the amount should reference the names in the From and To columns:

What is the function to use in the excel file?

0 1 General Appliance Corporation To Amount From Marietta cleveland 3 Data Distribution Center Plant Cleveland Baltimore Chicago Phoenix Capacity 350 tta baltimore marietta chicago marietta phoenix minneapolis cleveland minneapolis baltimore minneapolis chicago minne8 Marietta $ 12.60 S 14.35 $ 11.52 8.11 17.58 17.9.2 1200 Minneapolis $9.75 $ 16.26 350 850 150 Demand 150 500 1000 10 Model 500 150 Distribution Center oenix Plant Cleveland Baltimore Chicago Phoenix Total shipped 13 Marietta Minneapolis 350 0 850 150 1000 1200 150 150 Demand met 15 16 17 18 19 20 21 Cost Total 28,171 23 25 26 27 28 29 31 32 35 36 37 38 39

Explanation / Answer

Note that here the reference data is not split into rows and columns.

So, you will have to apply separate functions for Marietta and Minneapolis.

For Marietta, enetr the function -

=INDEX($J$4:$J$7,MATCH(B$12,$I$4:$I$11,0),1)

in cell B13

and for Minneapolis, enter the function -

=INDEX($J$8:$J$11,MATCH(B$12,$I$4:$I$11,0),1)

in cell B14.

Then collectively drag B13 and B14 towrads right to complete the table. You would get what is required.

--------------

Please ask if you have any doubt.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.