15. Final Project: FreshGo Maharashtra Monthly Report
15.3 Stage 2 – Tables and Lookup Formulas
Steps in Excel
- Convert
Stores,ProductsandTargetsto Tables:tblStores,tblProducts,tblTargets(Ctrl + T, Table Design › Table Name). - Add calculated columns to
tblOrders:- Platform:
=XLOOKUP([@[Store ID]],tblStores[Store ID],tblStores[Platform],"Not found")(Microsoft 365 / Excel 2021+) - Category:
=XLOOKUP([@Product],tblProducts[Product],tblProducts[Category],"Not found") - Delivery Fee:
=XLOOKUP([@Amount],{0;99;199;499},{30;25;15;0},,-1)(match mode −1 = exact or next smaller) - On Time:
=IF([@[Delivery Mins]]="","",IF([@[Delivery Mins]]<=12,"Yes","No"))
- Platform:
- Older Excel: replace XLOOKUP with
=IFERROR(INDEX(tblStores[Platform],MATCH([@[Store ID]],tblStores[Store ID],0)),"Not found"). - Check:
=COUNTIF(tblOrders[Platform],"Not found")must be 0.
Worked example – fee check. An order of ₹150 → fee ₹25; ₹199 → ₹15; ₹520 → ₹0; ₹64 → ₹30 (same slabs as Module 4).
Ravindra Bagale's Tip
Many students apply a lookup formula and never check how many "#N/A" there are – then a "(blank)" or "#N/A" category shows up in the pivot. Always give "Not found" in if_not_found and check with COUNTIF that the count is 0. If it isn't 0, find what is missing in the master Table – don't change the formula.
Ravindra Bagale's Tip – मराठी
बरेच students lookup formula लावतात आणि "#N/A" किती आहेत ते बघतच नाहीत – मग pivot मध्ये एक "(blank)" किंवा "#N/A" category येते. नेहमी if_not_found मध्ये "Not found" द्या आणि COUNTIF ने चेक करा की ते 0 आहेत. 0 नसेल तर master Table मध्ये काय missing आहे ते शोधा, formula नाही बदलायचा.
Ravindra Bagale's Tip – हिंदी
बहुत से students lookup formula लगाते हैं और देखते ही नहीं कि कितने "#N/A" हैं – फिर pivot में एक "(blank)" या "#N/A" category आ जाती है. हमेशा if_not_found में "Not found" दो और COUNTIF से check करो कि वे 0 हैं. 0 नहीं हैं तो master Table में क्या missing है वह ढूँढो, formula मत बदलो.
Practice task
Add the Platform, Category, Delivery Fee and On Time columns to tblOrders, confirm there are zero "Not found" values, and write the INDEX-MATCH version of the Platform formula in a note.
Loading practice tasks...
- Total orders (Easy): Add up Orders in D2:D11. (sheet "Data")
- Total GMV (Easy): Add up GMV (£) in E2:E11. (sheet "Data")
- Grocery rows (Easy): How many rows have Category "Grocery"? (sheet "Data")
- Max GMV (Easy): Largest GMV (£) in E2:E11. (sheet "Data")
- Pune rows (Easy): How many rows have City "Pune"? (sheet "Data")
- Pune GMV (Medium): Total of GMV (£) where City is "Pune". (sheet "Data")
- Grocery orders (Medium): Total of Orders where Category is "Grocery". (sheet "Data")
- Orders over 500 (Medium): How many SKUs have Orders > 500? (sheet "Data")
- GMV of BK-08 (Medium): GMV (£) for SKU "BK-08". (sheet "Data")
- Share Pune GMV (Hard): Pune GMV / all GMV, 3 decimals. (sheet "Data")
- Avg AOV (Hard): Average of AOV (£) in F2:F11, rounded to 1 decimal. (sheet "Data")
- Above avg GMV? (Hard): "High" if E2 > average GMV else "OK". (sheet "Data")
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
