16. Practice Exercises with Answer Hints
16.3 Module 4: Lookups
- 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+). - 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)). - Return the Store ID when you know the City Manager (lookup to the left). Hint: INDEX-MATCH or XLOOKUP – VLOOKUP cannot look left.
- 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. - 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)). - Find which Order IDs in the MarketRush list are missing from the master list.
Hint:
=IF(ISNA(MATCH(A2,Master[Order ID],0)),"Missing","")orCOUNTIF(...)=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.
Ravindra Bagale's Tip – मराठी
बरेच students VLOOKUP मध्ये शेवटचा argument (FALSE) विसरतात – आणि Excel approximate match करून चुकीचं पण "बरोबर दिसणारं" उत्तर देतो. Exact match साठी नेहमी FALSE किंवा 0 द्या; XLOOKUP मध्ये default exact आहे. #N/A आला तर आधी spaces आणि text-number फरक चेक करा.
Ravindra Bagale's Tip – हिंदी
बहुत से students VLOOKUP में आख़िरी argument (FALSE) भूल जाते हैं – और Excel approximate match करके गलत पर "सही दिखने वाला" जवाब देता है. Exact match के लिए हमेशा FALSE या 0 दो; XLOOKUP में default exact है. #N/A आए तो पहले spaces और text-number का फ़र्क check करो.
Loading practice tasks...
- VLOOKUP name (Easy): Name for ID "S103" with VLOOKUP.
- XLOOKUP value (Easy): The Avg deal value for ID "S103".
- Position (Easy): Position of "Hannah Lee" in B2:B9.
- INDEX and MATCH (Medium): ID for name "Hannah Lee" using INDEX and MATCH.
- Not found message (Medium): Deals won for ID "Z999", or "Not found".
- IFNA with VLOOKUP (Medium): VLOOKUP of "Z999" in A2:E9 column 4, or "Missing".
- XMATCH (Medium): Position of ID "S103" in A2:A9.
- Slab rate (Hard): Rate for E2 from the slab table G2:H6.
- Slab amount (Hard): E3 times its slab rate.
- Two-way lookup (Hard): Value in A2:E9 at the row of "Hannah Lee" and column "Deals won".
- Lookup the top (Hard): Name with the largest Deals won.
Worked example — module 4 lookups
Why
Rohan Patil in Pune and Liam Murphy abroad both need a clear, repeatable way to use module 4 lookups on a GreenField Dairy sheet without breaking totals.
What
Module 4 Lookups is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for module 4 lookups.
- Enter a small made-up value for Rohan Patil using € where money appears.
- Add a second value for Liam Murphy using £ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once module 4 lookups is correct on one row, copy the same pattern down the column.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
