VLOOKUP is usually used when the lookup value and the result are in the same row. But if they are in different rows, can VLOOKUP handle it?
No — VLOOKUP can only search within the same column, so this time we introduce using INDEX + MATCH together.
The formula is:
=INDEX($F$3:$F$12,MATCH(B2,$E$2:$E$11))
- MATCH(B2,$E$2:$E$11) asks Excel to search for the value in B2 within E2:E11 — it finds "D" at the 10th position, returning10。
- Then =INDEX($F$3:$F$12,10) tells Excel to take the 10th cell from F3:F10 as the result, yielding "DD".
- The key is that MATCH's range starts at row 2 while INDEX's range starts at row 3, producing a misalignment. VLOOKUP cannot reproduce this effect.
- So INDEX + MATCH's lookup concept is based on relative positions. Once you understand this, you'll find INDEX + MATCH may be more useful than VLOOKUP!
