Index/Match usage

[JAV]

Hi, I am addicted to the combined Index/Match formula for pulling data from one place to another. There is one thing that drives me crazy... how to drag the formula across with an increase in the column number to pull data from. Example wanting to pull data from sheet 1 to sheet 2 on spreadsheet =INDEX(Sheet2!$A:$AA,MATCH($B1,Sheet2!$A:$A,0),2)... what I want to do is pull this formula across a set of cells increasing the Column (2) as I go. There are times when I have to do this for 52 columns (ie a year of weeks). Today I copy the and then edit 51 cells, changing the ,2) to ,3) ,4).... ,53) How can I get the column to increase as I bring it across the columns?

 

[Domenic]

If, for example, the formula is going to entered in C1 and copied across...

=INDEX(Sheet2!$A:$AA,MATCH($B1,Sheet2!$A:$A,0),COLUMNS($C1:C1))

 

[seenfresh]

Use the Column() function...

http://office.microsoft.com/en-us/excel/HP100624091033.aspx

 

[JAV]

Amazing ... thanks, this mystery has been solved.