Return stock for the selected SKU
How many units of SKU-22 are in stock?
Formula: =VLOOKUP(E2,A2:D5,4,FALSE) → 75
Hands-on Excel exercises
VLOOKUP Practice
Practice VLOOKUP in a live spreadsheet with exact and approximate matches, missing-value handling, and guided lookup exercises with answers.
VLOOKUP is still common in inherited workbooks, exports, and interview exercises. Its logic is compact: search the first column of a table and return a value from a numbered column to the right. The live spreadsheet below looks up a catalog price by ID. Change the ID, the return-column number, or the final match argument and watch what changes.
Good VLOOKUP practice must include the failure modes. An omitted match argument can produce a plausible but wrong result. A table that shifts can break a hard-coded column number. A key stored as text will not necessarily match a numeric key that looks identical. The related exercises cover exact matches, ordered range lookups, error handling, cross-sheet tables, and alternatives such as XLOOKUP and INDEX with MATCH.
Work from a known key before trying a missing one. Confirm that the lookup column is the leftmost column of the selected table, count the return column from that same boundary, and lock ranges before copying the formula. Once you can explain those checks, you are ready to compare VLOOKUP with newer lookup patterns and choose based on the workbook you actually have.
Your turn
Your turn: in F2, use an exact VLOOKUP to return the price for the SKU in E2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | SKU | Item | Price | Stock | Find SKU | |
| 2 | SKU-11 | Notebook | 6 | 40 | SKU-22 | |
| 3 | SKU-22 | Marker | 3 | 75 | ||
| 4 | SKU-33 | Folder | 5 | 28 | ||
| 5 | SKU-44 | Tape | 4 | 52 |
Not yet solved.
Answer: =VLOOKUP(E2,A2:D5,3,FALSE)
Related exercises
Open a task in its lesson.
- In F2:F4, look up the item name for each code in column E.
- In G2:G4, look up the item price.
- In F2:F6, find each customer's discount from the scale on the left.
- In G2:G6, calculate the amount to pay after the discount.
- In D2:D6, calculate the average sale (revenue / units sold). Where there were no sales, show 0 instead of an error.
- In F2:F5, look up the item name for each SKU. If the SKU does not exist, show "Not in catalog".
- In G2:G4, find the employee ID for each name. The ID is to the left of the name, so VLOOKUP will not help.
- In E10:E13, find the monthly price with INDEX + MATCH.
Worked answers
Try the spreadsheet first, then compare your formula and result.
Label a missing SKU
What should the sheet show when SKU-99 is absent?
Formula: =IFERROR(VLOOKUP("SKU-99",A2:D5,2,FALSE),"Not found") → Not found
Frequently asked questions
Why does VLOOKUP return the wrong row?
The usual cause is approximate matching when exact matching was intended. Use FALSE for IDs and verify that key types match.
Can VLOOKUP look to the left?
Not directly. Reorder the source table or use XLOOKUP or INDEX with MATCH when the return column is left of the key.
When is approximate VLOOKUP appropriate?
It suits ordered thresholds such as tax, discount, or grade bands. The threshold column must be sorted and tested at boundaries.