Stockertim Dictionary · Technology
Column index number
Also called col_index_num, column index, VLOOKUP column number
In short
The third argument of VLOOKUP: the position of the column to return, counted from the first column of the lookup table, which goes wrong when columns are inserted or removed.
- Intermediate
- Concept
- Checked
In detail
VLOOKUP takes four arguments: the lookup value, the table, the column index number and the match type. In =VLOOKUP(A2,$F$2:$J$200,3,FALSE) the function searches the first column of F2:J200, column F, and returns the value from the third column of the table, which is column H. The count always starts at 1 for the column being searched.
The number is fixed in the formula, which makes it fragile. If someone inserts a column between F and H, the price that was in H moves to I, but the formula still says 3 and now returns whatever sits in the new column H. Nothing turns red; the sheet simply shows different numbers. Deleting a column inside the table has the same effect in reverse. A number larger than the width of the table gives #REF!, and a number below 1 gives #VALUE!.
The design also means VLOOKUP can only return columns to the right of the searched column. Data with the key in the middle has to be rearranged, or a different method used.
Several approaches remove the hard-coded number. XLOOKUP names the return column directly, so it follows the column wherever it moves. INDEX with MATCH does the same, as in =INDEX(H2:H200,MATCH(A2,F2:F200,0)). If VLOOKUP stays, a MATCH on the header row can calculate the column number from the column's name instead of a typed figure.
Worked example
A stationery wholesaler in Leicester has a table F:J with Code in F, Name in G and Price in H, and uses VLOOKUP with a column index number of 3.
- Before: the third column is H, so the formula returns the price, 2.40
- A colleague inserts a Supplier column at G; Name moves to H and Price to I
- After: the third column is now H, so the formula returns the product name instead of the price
The insertion silently changed what the formula returns.
In practice
Older workbooks are full of VLOOKUPs with typed column numbers. Reviewers check them whenever a source table changes shape, and many teams replace them with XLOOKUP or INDEX MATCH when they next edit the sheet.
Watch out
- Inserting or deleting a column inside the table changes the result without an error.
- VLOOKUP cannot return a column to the left of the one it searches.
Learn it properly
Official sources
Facts last checked on 9 November 2026.
Education, not advice: the Stockertim Dictionary explains what terms mean. It never recommends buying or selling anything.