Sick of having to update VLOOKUP formulas with a new Index after inserting another column?

The third parameter to the V/HLOOKUP functions in Microsoft Excel take the column index from the table array to return. For years I specified this parameter by counting the columns and hard-coding the number, which is a pain if a column is inserted or deleted changing the index!

To get around having to specify this as a hard-coded parameter, you can use the COLUMNS function to count the columns in a range. As you’re passing column references to this function, the references will update if new columns are inserted or deleted, automatically changing the index.

Say you want to return the 3rd column from the Data Sheet, you can do this:

=VLOOKUP($A2,Data!$A:C,COLUMNS(Data!$A:C),FALSE)

If you were to delete column B, the formula would automatically update to the following, still returning the same data

=VLOOKUP($A2,Data!$A:B,COLUMNS(Data!$A:B),FALSE)

Single VLOOKUP+COLUMNS

Leave a Reply