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!) |
About this data: Teaching sample for a fictional shop “Maple Bakery Co”. Figures and names (e.g. Neha Shah) are made up for this lesson on vlookup approximate match and the column index.
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.
Loading practice tasks...
- Manager with column 5 (Easy): Find the City Manager of AMN-NGP-01.
- FD: rate for 100 days (Easy): Find the FD rate for 100 days.
- FD: rate for 365 days (Easy): Find the FD rate for 365 days.
- Find the column number (Medium): Find the position of "City Manager" in A1:F1.
- Fee for ₹150 (Medium): Find the fee for an order of ₹150 from H2:I5.
- FD: rate for 500 days (Medium): Find the FD rate for 500 days.
- FD: rate for 1000 days (Medium): Find the FD rate for 1000 days.
- Safe lookup (Hard): Find the City Manager of BLK-KOP-01, using MATCH for the column number.
- Fee for ₹1,299 (Hard): Find the fee for ₹1299.
- FD: safe column number (Hard): Find the rate for 200 days. Find the column number by looking for the heading "FD rate %" in row 1.
Worked example — vlookup approximate match and the column index
Why
Sana Sheikh in Pune and James O'Neill abroad both need a clear, repeatable way to use vlookup approximate match and the column index on a HomeNest Supplies sheet without breaking totals.
What
Vlookup Approximate Match And The Column Index is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for vlookup approximate match and the column index.
- Enter a small made-up value for Sana Sheikh using £ where money appears.
- Add a second value for James O'Neill using ₹ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once vlookup approximate match and the column index is correct on one row, copy the same pattern down the column.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
