Ravindra BagaleCourses & study guides

16. Practice Exercises with Answer Hints

16.3 Module 4: Lookups

By Last updated:

  1. Get the City Manager for a Store ID from tblStores. Hint: =XLOOKUP(F2,tblStores[Store ID],tblStores[City Manager],"Not found") (Microsoft 365 / Excel 2021+).
  2. Do the same with VLOOKUP and with INDEX-MATCH. Hint: =VLOOKUP(F2,tblStores,5,FALSE); =INDEX(tblStores[City Manager],MATCH(F2,tblStores[Store ID],0)).
  3. Return the Store ID when you know the City Manager (lookup to the left). Hint: INDEX-MATCH or XLOOKUP – VLOOKUP cannot look left.
  4. Delivery fee from the slabs 0→30, 99→25, 199→15, 499→0 for an order of ₹250. Hint: approximate match: =VLOOKUP(250,FeeSlabs,2,TRUE) → ₹15.
  5. Two-way lookup: sales for City = Nashik and Month = Mar-2026 from a matrix. Hint: =INDEX(B2:M7,MATCH("Nashik",A2:A7,0),MATCH("Mar-2026",B1:M1,0)).
  6. Find which Order IDs in the Amazon Now list are missing from the master list. Hint: =IF(ISNA(MATCH(A2,Master[Order ID],0)),"Missing","") or COUNTIF(...)=0.

Ravindra Bagale's Tip

Many students forget the last argument (FALSE) in VLOOKUP – and Excel does an approximate match and gives a wrong answer that "looks right". For an exact match, always give FALSE or 0; in XLOOKUP, exact is the default. If you get #N/A, first check for spaces and text-vs-number differences.