# Thread: Increment column Ref by 2 when copying formula from Left to Right

1. ## Increment column Ref by 2 when copying formula from Left to Right

I have the following formula in B3

Code:
`=INDEX(Salesbr!M:M,MATCH(\$A\$3,salesBR!\$B:\$B,0))`
I need M:M to increment by two columns when I copy it into C3 i.e. =INDEX(Salesbr!O:O,MATCH(\$A\$3,salesBR!\$B:\$B,0))

I then need O:O to increment by 2 columns when I copy into D3 etc

Code:
` =INDEX(Salesbr!Q:Q,MATCH(\$A\$3,salesBR!\$B:\$B,0))`

It would be appreciated if someone could assist me  Reply With Quote

2. Is B3 an ARRAY FORMULA, Howard?

I don't particularly like this array formula, but it works:

Code:
`=INDEX(INDIRECT("Salesbr!"&CHOOSE(COLUMN()-1,"M","O","Q","S","U")&":"&CHOOSE(COLUMN()-1,"M","O","Q","S","U")),MATCH(\$A\$3,Salesbr!\$B:\$B,0))`
CTRL+Shift+Enter  Reply With Quote

3. ## The Following User Says Thank You to kweaver For This Useful Post:

HowardC (2018-04-16)

4. Howard,

Only if KW's approach does not work, you can try this Code by giving it a short cut CTRL-Z

Code:
```Public Sub CopyFormula()
x = ActiveCell.Formula
s = Asc(Split(Split(Split(x, ",")(0), "!")(1), ":")(0)) + 2
ActiveCell.Offset(0, 1).Formula = "=INDEX(salesBR!" & Chr(s) & ":" & Chr(s) & ",MATCH(\$A\$3,salesBR!\$B:\$B,0))"
End Sub```
Select B3 with the formula then click CTRL-Z and the formula will be copied to C3 (one cell to the right) with the advancing Columns by 2 and so on .

HTH
Maud  Reply With Quote

5. ## The Following User Says Thank You to Maudibe For This Useful Post:

HowardC (2018-04-16)

6. Thanks for the help. much appreciated  Reply With Quote

7. As a regular formula, you could add a column to your INDEX function and widen the original range:

=INDEX(SalesBr!\$M:\$IV,MATCH(\$A\$3,SalesBr!\$B:\$B,0), COLUMNS(\$A\$1:A\$1)*2-1)  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•