Ravindra BagaleCourses & study guides मराठी Track your progress

4. Lookup Functions

Practice and Answer Key: XLOOKUP and Delivery Fee Slabs

By Last updated:

Goal of this practice: Bring the City Manager with XLOOKUP and add the delivery fee from a slab table.

Suppose Shahrukh runs the evening shift report. He has five orders with only a Store ID and an amount. He needs the City Manager, the delivery fee and the amount the customer pays. Use the Stores sheet from this chapter and the FeeSlabs table from section 4.9. All data is fictional.

A: Order ID B: Store ID D: Amount (₹)
BLK-2101 BLK-NSK-01 75
AMN-2102 AMN-NGP-01 199
BLK-2103 BLK-PUN-07 140
BLK-2104 BLK-KOP-01 650
AMN-2105 AMN-SLP-01 98

Exercise (one under the other):

  1. Type the five rows. Keep column C empty for the manager.
  2. In C2, use XLOOKUP on the Stores sheet to return the City Manager. Show "Not in master" when the store is missing.
  3. In E2, return the fee with an approximate XLOOKUP on FeeSlabs (match_mode -1).
  4. In F2, add amount and fee.
  5. Copy C2, E2 and F2 down to row 6.
  6. Total column F.

Expected result (check your numbers):

Order ID Store ID City Manager Amount (₹) Fee (₹) Customer pays (₹)
BLK-2101 BLK-NSK-01 Zoya 75 30 105
AMN-2102 AMN-NGP-01 Amir 199 15 214
BLK-2103 BLK-PUN-07 Not in master 140 25 165
BLK-2104 BLK-KOP-01 Rani 650 0 650
AMN-2105 AMN-SLP-01 Salman 98 30 128

Total customer pays = 105 + 214 + 165 + 650 + 128 = ₹1,262. ₹98 is one rupee below the ₹99 slab, so it pays ₹30.

Answer key (open after you try) 
C2: =XLOOKUP(B2, Stores!$A$2:$A$8, Stores!$E$2:$E$8, "Not in master")
E2: =XLOOKUP(D2, FeeSlabs!$A$2:$A$5, FeeSlabs!$B$2:$B$5, , -1)
F2: =D2+E2
F7: =SUM(F2:F6)

BLK-PUN-07 is not in the Stores sheet, so C4 shows the message. Add it to the master, and the name appears.

Common mistakes:

What you see Why Fix
#N/A in C4 The 4th argument (if_not_found) is missing Add "Not in master" as the 4th argument
Fee is #N/A for every row match_mode was left as exact match (0) Put -1 as the 5th argument: , , -1
Wrong fee for ₹199 Slab limits typed as text like "199–498" Type only the lower limit as a number
Results shift when you copy down The lookup ranges are not locked Press F4 to add $ signs

Ravindra Bagale's rule: every lookup gets a message for "not found", so a missing master row never hides.

Thodkyaat sangaycha tar (quick recap)

Don't forget FALSE and $ in VLOOKUP, and remember the col_index trap; INDEX + MATCH is flexible in every version; XLOOKUP (365/2021+) is the easiest – if_not_found, match/search modes and multiple columns; for slabs use the lower limit + approximate match; when you see #N/A, find the cause first. Next up – data cleaning A to Z!

Loading practice tasks...

  1. Manager of order 1 (Easy): Find the manager for the store in B2.
  2. Total order amount (Easy): Find the total of C2:C6.
  3. Insurance: holder name (Easy): Find the Holder of POL-502.
  4. Missing store (Medium): Find the manager for B4. Show "Not in master" if missing.
  5. Fee for order 1 (Medium): Find the fee for the amount in C2.
  6. Insurance: premium rate (Medium): Find the premium rate for the age in Policies!C3.
  7. Insurance: unknown policy (Medium): Find the Holder of POL-509. Show "Not in master" if missing.
  8. Bill for order 2 (Hard): Find what the customer pays for order 2 (C3 + fee).
  9. Fee for order 5 (Hard): Find the fee for C6.
  10. Insurance: yearly premium (Hard): Find the yearly premium of POL-503: Policies!D4/100000 × rate for age Policies!C4.

See all practice tasks in the Task Hub →