Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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

=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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.