In order to get a Vlookup increment column number formula, i.e. as you drag the VLOOKUP formula across is automatically knows to increase the column number by 1 (or some other number), you need to change where Excel gets the column number from.
NEW: Go to VLOOKUP Explanations with downloadable exercises and detailed solution
In the VLOOKUP syntax of
VLOOKUP(lookup value, table array,col index number, [range_lookup])
most people type in a number for the col index number e.g. a 5 to represent the 5th column.
In order to make it automatic so that you can copy the formula across and have the numbers change you can look at creating a trigger row i.e. a row that contains the numbers of the columns you want to use. The VLOOKUP formula will then look at this row to determine which column to use. It is easier to explain if you watch the video clip on the VLOOKUP column index trick.
There are also other ways to do it. Depending on the structure of your spreadsheet you may be able to use the COLUMNS function (returns the number of the column it is in).
Another more sophisticated way would be to use a MATCH function and lookup up the column number based on the name of the column.