15. Final Project: Blinkit 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.