# Set a formula on many cells with minimal manual edits

I need to set the following formula on many cells:

`````` =VLOOKUP(MAX(Historical!\$A\$5:\$FZ\$99999),Historical!\$A\$5:\$FZ\$99999,7,1)
``````

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.

-

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

`=INDEX(Historical!B\$5:B\$99999,MATCH(MAX(Historical!\$A\$5:\$FZ\$99999),Historical!\$A\$5:\$A\$99999,1))`

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

`=VLOOKUP(MAX(Historical!\$A\$5:\$FZ\$99999),Historical!\$A\$5:\$FZ\$99999,ROWS(D\$2:D2)+1,1)`

assumes that you want to start the formula in D2

-

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.

-