📎 This article includes 1 downloadable practice file ↓
In this article
Your orders sheet has product codes; the prices live in a product list. Typing each price by hand is slow and error-prone. A lookup fetches it: “find this code in that list and bring back the rate”. It’s the function that turns Excel users into the person everyone asks for help.
VLOOKUP: exact match
=VLOOKUP(lookup_value, table, column_number, FALSE)
=VLOOKUP(B2, Products!$A$2:$D$7, 3, FALSE)Read it as: look for B2 in the first column of the Products table, and return the value from its 3rd column (Rate). FALSE means exact match. Always type FALSE for codes, names and IDs.
| Code | Product | Rate | GST |
|---|---|---|---|
| P101 | Laptop | 42,000 | 18% |
| P103 | Printer | 9,500 | 18% |
Lock the table with $ (Lesson 11) so it doesn’t slide when you copy the formula down.
XLOOKUP: the modern way (Excel 2021 and 365)
=XLOOKUP(lookup_value, lookup_column, return_column, [if_not_found])
=XLOOKUP(B2, Products!A2:A7, Products!C2:C7)You point at the column to search and the column to return. Exact match by default. It can look left (return a column before the search column), it doesn’t break when columns are inserted, and it has a built-in “not found” message:
=XLOOKUP(B5, Products!A2:A7, Products!B2:B7, "Not found")
=XLOOKUP("Router", Products!B2:B7, Products!A2:A7) ' name → code (looking left)Use the result in a calculation
=C2 * XLOOKUP(B2, Products!A2:A7, Products!C2:C7) ' qty × looked-up rateWhen a code isn’t found
VLOOKUP and XLOOKUP without the 4th argument show #N/A. That’s useful: it means the code is missing from the master list or has a typo. With VLOOKUP, show a friendly message using IFNA:
=IFNA(VLOOKUP(B5, Products!$A$2:$D$7, 2, FALSE), "Not found")Don’t hide every #N/A with 0. A missing price silently becoming zero is how invoices go out wrong.
Approximate match: slabs and bands
For discount slabs, tax slabs or grades you want the band a value falls into, not an exact match. Put the lower limit of each band in the first column, sorted smallest to largest:
| Order value from | Discount |
|---|---|
| 0 | 0% |
| 10,000 | 2% |
| 50,000 | 5% |
| 1,00,000 | 8% |
=VLOOKUP(85000, Slabs!$A$2:$B$5, 2, TRUE) ' → 5%
=XLOOKUP(85000, Slabs!A2:A5, Slabs!B2:B5, , -1) ' -1 = exact or next smaller85,000 isn’t in the table, so Excel takes the largest limit not above it: 50,000 → 5%. More in approximate match for tax slabs.
Where beginners go wrong
| Problem | Cause | Fix |
|---|---|---|
| #N/A though the code is there | Extra space, or number vs text (“101” vs 101) | TRIM; make both the same type |
| Wrong price returned | Missing FALSE, so VLOOKUP did an approximate match on unsorted data | Always FALSE for codes |
| Works in row 2, breaks lower down | Table range not locked with $ | $A$2:$D$7, or use a Table |
| #REF! | Column number bigger than the table width | Count columns, or use XLOOKUP |
Practice
Download this lesson’s workbook below. It has a product master, an orders list with one code that doesn’t exist, and a discount slab table. Type your answers in the yellow column; the Check column turns green when you’re right, and the Answers sheet shows a working formula for every task.
📎 Practice files for this article
- 📗Lesson 14 practice workbookA product master, orders with codes (one missing) and a discount slab table: 10 lookup tasks.⬇ XLSX · 15 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.