4.9 Approximate Lookup: Delivery Fee Slabs
Quick-commerce apps charge a delivery fee based on order value. Here is a fictional slab table FeeSlabs (sorted ascending on Min Order):
| Min Order (₹) | Fee (₹) | Meaning |
|---|---|---|
| 0 | 30 | Orders below ₹99 |
| 99 | 25 | ₹99 – ₹198 |
| 199 | 15 | ₹199 – ₹498 |
| 499 | 0 | Free delivery from ₹499 |
Three ways to get the fee for Amount in G2:
=VLOOKUP(G2, FeeSlabs!$A$2:$B$5, 2, TRUE)
=INDEX(FeeSlabs!$B$2:$B$5, MATCH(G2, FeeSlabs!$A$2:$A$5, 1))
=XLOOKUP(G2, FeeSlabs!$A$2:$A$5, FeeSlabs!$B$2:$B$5, , -1)
| Amount | Fee |
|---|---|
| ₹64 | ₹30 |
| ₹150 | ₹25 |
| ₹199 | ₹15 (exact slab boundary) |
| ₹270 | ₹15 |
| ₹1,299 | ₹0 |
The same pattern works for commission slabs, rider incentive bands, discount tiers or grading.
Ravindra Bagale's Tip
In a slab table, many students write the range as text like "99–198" – Excel doesn't understand that. Write only the lower limit as a number and keep the table sorted ascending. VLOOKUP TRUE gives the wrong fee on an unsorted table, with no error – that's why XLOOKUP −1 is safer, because it doesn't depend on sorting.
Ravindra Bagale's Tip – मराठी
Slab table मध्ये बरेच students "99–198" असा range text मध्ये लिहितात – Excel ला तो समजत नाही. फक्त lower limit number मध्ये लिहा आणि table ascending sort ठेवा. VLOOKUP TRUE unsorted table वर चुकीची fee देतो, error नाही – म्हणून XLOOKUP −1 सुरक्षित आहे, कारण तो sort वर अवलंबून नाही.
Ravindra Bagale's Tip – हिंदी
Slab table में बहुत से students "99–198" जैसी range text में लिखते हैं – Excel इसे नहीं समझता. सिर्फ़ lower limit number में लिखो और table ascending sort रखो. VLOOKUP TRUE unsorted table पर गलत fee देता है, error नहीं – इसलिए XLOOKUP −1 ज़्यादा सुरक्षित है, क्योंकि वह sort पर निर्भर नहीं है.
Practice task
Create a rider incentive slab: 0–19 deliveries ₹0, 20–29 ₹200, 30–39 ₹400, 40+ ₹700. Calculate the incentive for ten riders with all three methods and check that they agree.