Lookups
Lessons
Practice file
⬇ Download m4-lookups-practice.xlsxLookups practice workbook
Use m4-lookups-practice.xlsx for all four lessons. Fill the yellow cells, then check the Answers sheet. Its formulas are text examples to copy into the indicated cells, with expected results for the original inputs.
| Lesson | Sheet | Practice |
|---|---|---|
| 1 | VLOOKUP | Look up the code in H1, enter quantity in H5, and fill product, price, stock and amount. Test zero stock and a missing code. Insert a column on a copy of the sheet. |
| 2 | INDEX MATCH | Create dropdowns in H1/H2 for product name and heading; write a two-way formula in H4 and a left lookup in H6. |
| 3 | XLOOKUP | Spill four columns from H4:K4; leave the spill destination empty. Practice wildcards, missing codes and XMATCH. |
| 3 | Stock History | Compare first and last matches against repeated Mobile rows; the newest record is at the bottom. |
| 4 | Brackets | Use marks in D2, sales in C2 and illustrative income in N2. Test three lookup methods, commissions and progressive slabs. |
| 4 | Shipping | Calculate ten parcel charges, including weights exactly at the 1, 5 and 10 kg boundaries. |
हिंदी: पीले सेल में फ़ॉर्मूले भरें और Answers से नतीजे मिलाएँ। शीट के नाम और सेल रेफ़रेंस दोनों भाषाओं में समान हैं। INDEX MATCH अभ्यास में H1 प्रोडक्ट का नाम है; VLOOKUP और XLOOKUP में H1 कोड है।
The workbook also includes Read Me instructions. XLOOKUP/XMATCH require Excel 2021 or later, Microsoft 365, or Excel for the web; VLOOKUP/INDEX+MATCH alternatives cover the basic lookups and bracket calculations for older versions. Examples use commas; adapt separators to your Excel locale if needed.
Tax slabs are fictional practice data, not current tax law, with no cess or rebate. The workbook has no macros or external connections.