4.2 VLOOKUP Approximate Match and the Column-Index Trap
In short: Approximate match (TRUE) finds the largest value less than or equal to the lookup value.
Approximate match (TRUE) finds the largest value less than or equal to the lookup value. The first column must be sorted ascending. It is used for slabs and bands (4.9), never for IDs.
The column-index trap. col_index_num is a hard-coded number. If someone inserts a column in Stores (say Pincode after Area), column 5 is no longer City Manager – your formula silently returns the pincode.
| Before insert | After inserting "Pincode" as column E |
|---|---|
=VLOOKUP(G2,Stores!$A$2:$F$8,5,FALSE) → Amir |
same formula → 440010 (wrong!) |
Ways to avoid it:
- Use
MATCHfor the column number:=VLOOKUP(G2,Stores!$A$2:$G$8,MATCH("City Manager",Stores!$A$1:$G$1,0),FALSE) - Use INDEX + MATCH (4.4) or XLOOKUP (4.6), which point to the return column directly.
Other VLOOKUP limits: it cannot look left (return a column before the lookup column), it returns only the first match, and a col_index larger than the table width gives #REF!.
Ravindra Bagale's Tip
Khup students col_index_num bottanne column mojun lihitat – aani table madhe navin column aala ki sagle reports chukiche hotat, tehi error na yeta. Col index manually mojnyapeksha MATCH ne header shodha, kiwa XLOOKUP vapra. Interview madhe "VLOOKUP chya limitations" vicharlya tar he pahila mudda sanga.
Practice task
Write a VLOOKUP that returns City Manager, insert a Pincode column in Stores, and watch it break. Rewrite it with MATCH so that it survives the insert.