Ravindra BagaleCourses & study guides Track your progress
Medium challenge

Medium: VLOOKUP column index off-by-one

By · Free ·

Absolute refsNumbers as textLeading spacesFilter left onChart range% vs decimalDuplicate headerROUND vs displayFreeze panesPaste valuesVLOOKUP & textVLOOKUP col indexHLOOKUP TRUESUMIF spacesCOUNTIFS ORIF nest orderDate serialINDEX MATCHWildcard COUNTIFMixed currencyCircular & datesCircular interest3D sum renameNamed range staleCF vs IFPivot staleHard-coded NPVValidation movedPQ step orderTyped-over formulaSUM wrong anchorsFiscal year Apr–MarFind all bugsInterview oral

Someone inserted a Region column. VLOOKUP still uses col_index_num=2, so FX-20 returns “West” instead of ₹1450. Change the index to 3 (LibreOffice: broken West, fixed 1450).

About this data: Made-up freight rates for Priya Nair (Kochi) and Omar Hassan (Cairo). Practice only.
Download broken workbook Download fixed (after you try)

Why → What → How

Why: Inserting a column silently breaks every VLOOKUP that hard-coded col_index_num.

What: Four freight codes. Broken B11 → West. Fixed B11 → 1450 (LO verified).

How:

  1. Open sheet Rates.
  2. See VLOOKUP return Region text.
  3. Count columns: Code=1, Region=2, Rate=3.
  4. Set col_index_num to 3, FALSE.

Ravindra Bagale’s Tip

Prefer INDEX+MATCH or Tables with structured refs so inserts hurt less.

Bugs explained (with mini examples)

Bug 1 — Stale col_index_num

After insert, 2 points at Region. Fix: =VLOOKUP(B10,$A$5:$D$8,3,FALSE)

Bug 2 — No test after schema change

Always re-check one known lookup after inserting columns.

Practice tasks (12)

  1. Task 1. What does broken B11 show for FX-20?
    Hint: West.
  2. Task 2. What should the rate be?
    Hint: 1450.
  3. Task 3. Why did col_index 2 break?
    Hint: Region inserted.
  4. Task 4. Write the fixed VLOOKUP.
    Hint: …,3,FALSE
  5. Task 5. Change lookup to FX-10 — expect 1200.
    Hint: Yes.
  6. Task 6. Interview sentence.
    Hint: “Col index not updated after insert.”
  7. Task 7. Would MATCH for Rate header help?
    Hint: Dynamic index.
  8. Task 8. Try approximate match TRUE here?
    Hint: No — codes exact.
  9. Task 9. Name columns 1–4.
    Hint: Code Region Rate Notes.
  10. Task 10. Add FX-50 row — extend range.
    Hint: $A$5:$D$9.
  11. Task 11. Bonus: INDEX+MATCH on Rate.
    Hint: INDEX Rate, MATCH code.
  12. Task 12. Document schema change in a note.
    Hint: A13.

In-browser formula check

Type the fixed formula you would put in the named cell. We check formula text — open the workbook for full recalculation.

Step-by-step solution (try first)
  1. Set col_index_num to 3.
  2. FX-20 → 1450. LO: broken West, fixed 1450.

Quick quiz

Q1. After inserting a column left of the return field, col_index usually must:
Q2. Broken result for FX-20 was:
Q3. Fixed LO-verified rate:
Q4. VLOOKUP fourth argument for exact codes:
Q5. Safer long-term pattern: