Skip to content

Home Guides VLOOKUP column index

Rule R2 (VLOOKUP column index integrity)

VLOOKUP column index risk (the silent “wrong column” failure)

If your VLOOKUP uses a hardcoded column number, your model can break the moment someone inserts a column — without throwing a single error.

Install Free Back to the 5 rules
On this page

Safer pattern (stop counting columns)

When you need a stable column selection, match the header instead of hardcoding col_index_num:

=INDEX(DataRange, MATCH(A2, KeyColumn, 0), MATCH("Bonus", HeaderRow, 0))

The exact ranges depend on your sheet layout. The key idea is: match the column by name, not position.

What this error is

A VLOOKUP where the col_index_num is invalid/out-of-bounds or brittle (likely to break with edits).

  • Out-of-bounds index (e.g., index 4 for a 3-column range)
  • Missing or blank index argument
  • Hardcoded index that becomes wrong after inserts/reorders

Why it is dangerous

  • Your formula keeps computing, so reviewers assume it is correct.
  • The returned value is from the wrong field (wrong column), not necessarily an error.
  • It often escapes into reporting because the sheet “looks fine”.

Mini scenario

You meant to return Bonus, but after a column insert, column 5 becomes Salary. Your sheet still shows numbers. They are just the wrong numbers.

Safer fix patterns

  1. Header-based selection: match the header text to choose the column.
  2. INDEX/MATCH: select both row and column by matching.
  3. XLOOKUP: when available, it avoids column counting and can be clearer.

How SheetSage detects this (R2)

SheetSage flags invalid/out-of-bounds VLOOKUP column index usage and marks it as a manual fix, because correct remediation depends on intent and sheet structure.

Related reading: reference drift and magic numbers.

FAQ

Why did Google Sheets not show an error?

Because the formula is still valid and still returns a value — it is just pulling from the wrong column after the table changed.

How do I prevent this forever?

Stop counting columns. Select columns by header name (MATCH) or use patterns like INDEX/MATCH that are resilient to inserts.

Are named ranges enough?

Named ranges can help keep tables stable, but they do not prevent “wrong column” failures if you still hardcode a column number.