Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need to set the following formula on many cells:


This formula looks up the most recent date in the History table, and returns the value specified. Here I'm returning column 7.

How can I paste the cells and auto increment the value where "7" is. In other words I need to paste that summary formula on several hundred cells and change the number 7 for each.

share|improve this question
up vote 2 down vote accepted

One option is to use INDEX and MATCH function instead of VLOOKUP. This can be beneficial in that you see explicitly which column the result is from......and by using the correct combination of $ signs you can copy across to increment the column each time, e.g. use this formula to return the value from column B


and copy across and the reference column will change to C, D etc.

If you want to copy the formula down then I would go with a variation on Lèse majesté's suggestion. using ROWS function in your original for added robustness, e.g. for B column again


assumes that you want to start the formula in D2

share|improve this answer

Normally, yes. As long as you're not using absolute references, whenever you paste or fill down, Excel (and most other spreadsheet programs, including Calc and Google Docs) will update the row number and column numbers in your references.

However, since you're using a function that receives a column index rather than a cell reference, it's a little trickier. I don't believe Excel knows to recognize that argument as a column reference, as there's no way for Excel to know whether you want an absolute or relative reference.

Luckily, there are the ROW and COLUMN functions, which, given a reference, will return the row and column indices respectively.

In your case, you simply need to replace 7 with COLUMN(A:1) or whatever cell reference you want to use. Excel will then change A:1 based on the relative position of the new cell.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .