Concept
We'll use the same product sheet from Lesson 1 (A1:E6).
1. INDEX — "give me the item at this position"
=INDEX(range, row_num, [col_num])
=INDEX(B2:B6, 3) → Chair (3rd item in B2:B6)
=INDEX(A2:E6, 4, 4) → 12000 (row 4, column 4 = Desk's price)
2. MATCH — "at which position is this item?"
=MATCH(lookup_value, lookup_array, [match_type])
=MATCH("Chair", B2:B6, 0) → 3
0 means exact match. Always use it for normal lookups. (Approximate match comes in Lesson 4.)
3. Combine them
MATCH finds the position, INDEX returns the value at that position:
=INDEX(D2:D6, MATCH("P103", A2:A6, 0)) → 4500
Read it as: "From the Price column, give me the item at the row where P103 is."
4. Left lookup — solves VLOOKUP limitation 1
Code of "Desk" (Code is left of Product):
=INDEX(A2:A6, MATCH("Desk", B2:B6, 0)) → P104
The search column and the return column are independent, so direction doesn't matter.
5. Safe against inserted columns — solves limitation 2
There's no column number. D2:D6 is a reference, so if a column is inserted, Excel automatically shifts it to E2:E6. The formula keeps returning the price.
6. Two-way lookup (row + column)
Put a code in H1 (e.g. P102) and a heading in H2 (e.g. Stock):
=INDEX(A2:E6, MATCH(H1, A2:A6, 0), MATCH(H2, A1:E1, 0)) → 40
The first MATCH finds the row, the second finds the column. Change H2 to "Price" and you get 18000 — no formula change needed.
7. Handling not found
=IFNA(INDEX(D2:D6, MATCH(H1, A2:A6, 0)), "Code not found")
Rules to remember
The INDEX range and the MATCH range must be the same size and start on the same row (D2:D6 with A2:A6, not A1:A6). Otherwise you'll get the value from the wrong row. And always lock ranges with $ before copying.
Common mistakes
Forgetting 0 in MATCH (default is 1 = approximate, gives wrong results on unsorted data). Mismatched range sizes. Using a whole table in INDEX but only one column number, and getting confused which column it returns.