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
Many students count the columns on their fingers to write col_index_num – and when a new column is added to the table, all the reports go wrong, without any error. Instead of counting the column index by hand, find the header with MATCH, or use XLOOKUP. If an interviewer asks about "the limitations of VLOOKUP", make this your first point.
Ravindra Bagale's Tip – मराठी
बरेच students col_index_num बोटांनी column मोजून लिहितात – आणि table मध्ये नवीन column आला की सगळे reports चुकीचे होतात, तेही error न येता. Col index हाताने मोजण्यापेक्षा MATCH ने header शोधा, किंवा XLOOKUP वापरा. Interview मध्ये "VLOOKUP च्या limitations" विचारल्या तर हा पहिला मुद्दा सांगा.
Ravindra Bagale's Tip – हिंदी
बहुत से students col_index_num उँगलियों पर column गिनकर लिखते हैं – और table में नया column आते ही सारी reports गलत हो जाती हैं, वह भी बिना error के. Col index हाथ से गिनने की बजाय MATCH से header ढूँढो, या XLOOKUP इस्तेमाल करो. Interview में "VLOOKUP की limitations" पूछी जाएँ तो यह पहला point बताओ.
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.