Ravindra BagaleCourses & study guides

15. Final Project: Blinkit Maharashtra Monthly Report

15.3 Stage 2 – Tables and Lookup Formulas

By Last updated:

Steps in Excel

  1. Convert Stores, Products and Targets to Tables: tblStores, tblProducts, tblTargets (Ctrl + T, Table Design › Table Name).
  2. 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"))
  3. Older Excel: replace XLOOKUP with =IFERROR(INDEX(tblStores[Platform],MATCH([@[Store ID]],tblStores[Store ID],0)),"Not found").
  4. 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

Khup students lookup formula lavtat aani "#N/A" kiti aahet te bagtach nahit – mag pivot madhe ek "(blank)" kiwa "#N/A" category yete. Nehmi if_not_found madhe "Not found" dya aani COUNTIF ne tapasa ki te 0 aahet. 0 nasel tar master Table madhe kay missing aahe te shodha, formula nahi badlaycha.

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.