Concept
1. What is a lookup?
A lookup is: "Find this item in a table, and bring back some information about it." Like finding a product code in a price list and reading its price.
2. Practice sheet (use it for the whole module)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Code | Product | Category | Price | Stock |
| 2 | P101 | Laptop | Electronics | 55000 | 12 |
| 3 | P102 | Mobile | Electronics | 18000 | 40 |
| 4 | P103 | Chair | Furniture | 4500 | 25 |
| 5 | P104 | Desk | Furniture | 12000 | 8 |
| 6 | P105 | Printer | Electronics | 9500 | 0 |
3. Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| Argument | Meaning | Example |
|---|---|---|
lookup_value |
what to find | "P103" or H1 |
table_array |
the table; search happens in its first column | A2:E6 |
col_index_num |
which column number of the table to return | 4 (Price) |
range_lookup |
FALSE = exact match, TRUE = approximate |
FALSE |
Price of P103:
=VLOOKUP("P103", A2:E6, 4, FALSE) → 4500
4. Make it dynamic
Type a code in H1, then:
=VLOOKUP(H1, $A$2:$E$6, 2, FALSE) → product name
=VLOOKUP(H1, $A$2:$E$6, 4, FALSE) → price
=VLOOKUP(H1, $A$2:$E$6, 5, FALSE) → stock
Lock the table with $ so it doesn't shift when you copy the formula.
If the code isn't in the table, you get #N/A. Wrap it with IFNA (Module 3, Lesson 7):
=IFNA(VLOOKUP(H1, $A$2:$E$6, 4, FALSE), "Code not found")
5. The 4 limitations
Limitation 1 — It can't look left. VLOOKUP only searches the first column of the table and returns columns to its right. To find the Code of "Desk", you can't, because Code (A) is left of Product (B).
Limitation 2 — Hard-coded column number. 4 means "4th column". If someone inserts a column between B and D, the 4th column is no longer Price, and the formula silently returns the wrong data.
Limitation 3 — Approximate match by default. If you skip the last argument, Excel assumes TRUE (approximate). On an unsorted table, this returns wrong answers without any error. Always write FALSE (or 0) for exact matches.
Limitation 4 — Only the first match. If a value appears more than once, VLOOKUP returns only the first one it finds, top to bottom. It can't give you the last match or all matches.
6. Why still learn it?
Millions of existing workbooks use VLOOKUP, and it works in every Excel version. You'll read and fix it at work even if you write XLOOKUP yourself.
Common mistakes
Forgetting FALSE. Not locking the table with $. Extra spaces or numbers stored as text in the lookup column, causing #N/A even though the value "looks" present. Counting the column number from column A of the sheet instead of the first column of table_array.