### [Solved] How do you copy an array formula down a colum?

Posted:

**Mon Aug 25, 2014 11:42 am**I have the following array formula in cell B2:

=INDEX($M$2:$O$110;MATCH(1;($M$2:$M$110=C2)*($N$2:$N$110=E2);0);3)

and I want to copy it down a column, so I want:

B2 {=INDEX($M$2:$O$110;MATCH(1;($M$2:$M$110=C2)*($N$2:$N$110=E2);0);3)}

B3 {=INDEX($M$2:$O$110;MATCH(1;($M$2:$M$110=C3)*($N$2:$N$110=E3);0);3)}

B4 {=INDEX($M$2:$O$110;MATCH(1;($M$2:$M$110=C4)*($N$2:$N$110=E4);0);3)}

etc

I enter the formula into B2, I press CTRL+SHIFT+ENTER, then I copy the formula down the B column. This only gives me:

={INDEX($M$2:$O$110;MATCH(1;($M$2:$M$110=C2)*($N$2:$N$110=E2);0);3)}

in each of the cells I copied to.

The relative reference to C2 and E2 seems to be treated as an absolute reference. I want E2 and C2 to change.

=INDEX($M$2:$O$110;MATCH(1;($M$2:$M$110=C2)*($N$2:$N$110=E2);0);3)

and I want to copy it down a column, so I want:

B2 {=INDEX($M$2:$O$110;MATCH(1;($M$2:$M$110=C2)*($N$2:$N$110=E2);0);3)}

B3 {=INDEX($M$2:$O$110;MATCH(1;($M$2:$M$110=C3)*($N$2:$N$110=E3);0);3)}

B4 {=INDEX($M$2:$O$110;MATCH(1;($M$2:$M$110=C4)*($N$2:$N$110=E4);0);3)}

etc

I enter the formula into B2, I press CTRL+SHIFT+ENTER, then I copy the formula down the B column. This only gives me:

={INDEX($M$2:$O$110;MATCH(1;($M$2:$M$110=C2)*($N$2:$N$110=E2);0);3)}

in each of the cells I copied to.

The relative reference to C2 and E2 seems to be treated as an absolute reference. I want E2 and C2 to change.