4.9 Approximate Lookup: Delivery Fee Slabs
Approximate lookup for fee slabs with example — in 30 seconds
What: An approximate lookup finds the slab whose lower limit is just below (or equal to) your value.
Why: Delivery fees, commissions and discounts come in slabs. One formula replaces a long nested IF.
Tiny example: ₹150 falls in the ₹99 slab, so the fee is ₹25. ₹199 is exactly on a slab boundary, so the fee is ₹15.
Approximate lookup for fee slabs with example — 30 सेकंदात
काय: Approximate lookup तुमच्या value च्या खाली (किंवा बरोबर) असलेली lower limit असलेला slab शोधतो.
का: Delivery fee, commission आणि discount slabs मध्ये असतात. एक formula लांबलचक nested IF ची जागा घेतो.
छोटं उदाहरण: ₹150 हा ₹99 च्या slab मध्ये येतो, म्हणून fee ₹25. ₹199 बरोबर slab च्या सीमेवर आहे, म्हणून fee ₹15.
Approximate lookup for fee slabs with example — 30 सेकंड में
क्या: Approximate lookup आपकी value से नीचे (या बराबर) वाली lower limit का slab ढूँढता है.
क्यों: Delivery fee, commission और discount slabs में होते हैं. एक formula लंबे nested IF की जगह ले लेता है.
छोटा उदाहरण: ₹150 ₹99 वाले slab में आता है, इसलिए fee ₹25. ₹199 ठीक slab की सीमा पर है, इसलिए fee ₹15.
First look at the data (before → after):
Expected result — Fee for each order amount
| G: Amount (₹) | H: Fee (₹) |
|---|---|
| 64 | 30 |
| 150 | 25 |
| 199 | 15 |
| 270 | 15 |
| 1,299 | 0 |
About this data: Teaching sample for a fictional shop “BrightLedger Finance”. Figures and names (e.g. Meera Joshi) are made up for this lesson on approximate lookup delivery fee slabs.
Before table and formula as text
Before — FeeSlabs (sorted ascending, fictional)
| A: Min Order (₹) | B: Fee (₹) |
|---|---|
| 0 | 30 |
| 99 | 25 |
| 199 | 15 |
| 499 | 0 |
About this data: Demo table only — not real customer or company data. Built to practise approximate lookup delivery fee slabs.
Formula / click path
H2: =XLOOKUP(G2, FeeSlabs!$A$2:$A$5, FeeSlabs!$B$2:$B$5, , -1)
match_mode -1 = exact match or next smaller item
Do it step by step:
- Type the slab table on a sheet named
FeeSlabs: lower limits in A2:A5, fees in B2:B5. - Keep the lower limits as numbers, sorted from small to big.
- In Orders H2, type
=XLOOKUP(G2, FeeSlabs!$A$2:$A$5, FeeSlabs!$B$2:$B$5, , -1). - Copy down.
- Test the boundaries: 98, 99, 198 and 199.
स्टेप बाय स्टेप करून बघा:
FeeSlabsनावाच्या sheet वर slab table type करा: lower limits A2:A5 मध्ये, fees B2:B5 मध्ये.- Lower limits numbers म्हणूनच ठेवा, लहान ते मोठे sort करून.
- Orders च्या H2 मध्ये
=XLOOKUP(G2, FeeSlabs!$A$2:$A$5, FeeSlabs!$B$2:$B$5, , -1)type करा. - खाली copy करा.
- सीमा test करा: 98, 99, 198 आणि 199.
स्टेप बाय स्टेप करके देखें:
FeeSlabsनाम की sheet पर slab table type करें: lower limits A2:A5 में, fees B2:B5 में.- Lower limits को numbers ही रखें, छोटे से बड़े sort करके.
- Orders की H2 में
=XLOOKUP(G2, FeeSlabs!$A$2:$A$5, FeeSlabs!$B$2:$B$5, , -1)type करें. - नीचे copy करें.
- सीमाएँ test करें: 98, 99, 198 और 199.
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 |
About this data: Made-up rows so you can copy the pattern for approximate lookup delivery fee slabs. Currencies may use ₹, £, € or $.
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 |
About this data: Fictional worksheet used in class. Safe to edit; nothing here is live production data.
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.
Loading practice tasks...
- Fee for ₹150 (Easy): Find the fee for an order of ₹150.
- Fee for ₹64 (Easy): Find the fee for ₹64.
- Courier: 0.3 kg parcel (Easy): Find the charge for 0.3 kg.
- Exactly on the edge (Medium): Find the fee for ₹199.
- Big order (Medium): Find the fee for ₹1299.
- Courier: 2.5 kg parcel (Medium): Find the charge for 2.5 kg.
- Courier: exactly 5 kg (Medium): Find the charge for 5 kg.
- The VLOOKUP way (Hard): Find the fee for ₹270 with VLOOKUP.
- Customer pays (Hard): Find what the customer pays for a ₹98 order (98 + fee).
- Courier: two parcels (Hard): Find the charge for 2 parcels of 0.75 kg each.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
