ASSIGNMENT – 11
Answer Key – MATCH & VLOOKUP with MATCH
Coffee Price List
| Coffee | SMALL | MEDIUM | LARGE |
|---|---|---|---|
| Cafe Latte | ₹2.75 | ₹3.50 | ₹4.25 |
| Cappuccino | ₹2.95 | ₹3.65 | ₹4.40 |
| Caramel Macchiato | ₹3.25 | ₹3.90 | ₹4.50 |
| Cafe Mocha | ₹3.00 | ₹3.75 | ₹4.35 |
| White Chocolate Mocha | ₹3.50 | ₹4.20 | ₹4.75 |
| Americano | ₹2.00 | ₹2.50 | ₹3.00 |
| Cinnamon Latte | ₹3.40 | ₹4.00 | ₹4.60 |
| Espresso | ₹2.25 | ₹2.75 | ₹3.25 |
| Iced Coffee | ₹2.50 | ₹3.25 | ₹3.95 |
| Cold Brew | ₹3.25 | ₹3.75 | ₹4.50 |
Q.1 Find the column number of SMALL.
Assuming the headings are in A1:
=MATCH("SMALL",A1:D1,0)Answer: 2
Q.2 Find the column number of MEDIUM.
=MATCH("MEDIUM",A1:D1,0)Answer: 3
Q.3 Find the column number of LARGE.
=MATCH("LARGE",A1:D1,0)Answer: 4
MATCH Answers
| Size | Column Number |
|---|---|
| SMALL | 2 |
| MEDIUM | 3 |
| LARGE | 4 |
Q.4 Cafe Mocha – MEDIUM
=VLOOKUP("Cafe Mocha",A2:D11,MATCH("MEDIUM",A1:D1,0),FALSE)Answer: ₹3.75
Q.5 Cappuccino – LARGE
=VLOOKUP("Cappuccino",A2:D11,MATCH("LARGE",A1:D1,0),FALSE)Answer: ₹4.40
Q.6 Americano – SMALL
=VLOOKUP("Americano",A2:D11,MATCH("SMALL",A1:D1,0),FALSE)Answer: ₹2.00
Q.7 White Chocolate Mocha – LARGE
=VLOOKUP("White Chocolate Mocha",A2:D11,MATCH("LARGE",A1:D1,0),FALSE)Answer: ₹4.75
Q.8 Cinnamon Latte – MEDIUM
=VLOOKUP("Cinnamon Latte",A2:D11,MATCH("MEDIUM",A1:D1,0),FALSE)Answer: ₹4.00
Q.9 Cold Brew – SMALL
=VLOOKUP("Cold Brew",A2:D11,MATCH("SMALL",A1:D1,0),FALSE)Answer: ₹3.25
Q.10 Caramel Macchiato – LARGE
=VLOOKUP("Caramel Macchiato",A2:D11,MATCH("LARGE",A1:D1,0),FALSE)Answer: ₹4.50
Final Answer Summary
| Question | Coffee | Size | Answer |
|---|---|---|---|
| Q1 | — | SMALL | Column 2 |
| Q2 | — | MEDIUM | Column 3 |
| Q3 | — | LARGE | Column 4 |
| Q4 | Cafe Mocha | MEDIUM | ₹3.75 |
| Q5 | Cappuccino | LARGE | ₹4.40 |
| Q6 | Americano | SMALL | ₹2.00 |
| Q7 | White Chocolate Mocha | LARGE | ₹4.75 |
| Q8 | Cinnamon Latte | MEDIUM | ₹4.00 |
| Q9 | Cold Brew | SMALL | ₹3.25 |
| Q10 | Caramel Macchiato | LARGE | ₹4.50 |
Important Concept
The advantage of using MATCH inside VLOOKUP is that we don’t have to manually enter the column number.
For example:
=VLOOKUP("Cafe Mocha",A2:D11,MATCH("MEDIUM",A1:D1,0),FALSE)Here, MATCH("MEDIUM",A1:D1,0) automatically finds that MEDIUM is column 3, and VLOOKUP uses that result to return the correct price.