VLOOKUP column index auto update with COLUMN function

In VLOOKUP formula, column index defines the column number of table from which the data is to be retrieved in results.

When we have to put VLOOKUP formula for many columns from same source table, we can use $ sign to lock the cell reference in formula. This will help us to drag the formula to all columns without editing it. But in that case column index will not be changed automatically and same result appears in all cells. You will have to update the column index manually for other columns to get the correct results. To avoid manual correction of formula, we can insert a row above the VLOOKUP table and define the column index in that and link that in the formula as below:

vlookup column

COLUMN function to update column index in VLOOKUP

Alternatively, we can also use COLUMN function to update column index. COLUMN function returns the column number for reference cell. The reference cell in COLUMN formula can be given to data table. When you drag or copy the formula to other cells, the reference in column formula will also be shifted to next cells and automatically changes its value. 

COLUMN function

We can use above COLUMN function in VLOOKUP as below:

=VLOOKUP($G7,$A$3:$E$52,COLUMN(B3),0)
VLOOKUP column


In case if the data table is not starting from column A, we can deduct numbers from column formula like COLUMN(B3)-3. This trick will save time in editing formula in every column.

Leave a Comment