Increase row value of formula as formula dragged across columns.

[alocke]

Hi,

I have the following formula:

=INDEX(Sheet1!$B18:$AT18,MATCH($AA2&$C2,Sheet1!$B$4:$AT$4 & Sheet1!$B$7:$AT$7,0))

When I place it into next column, I want it to look like this:

=INDEX(Sheet1!$B19:$AT19,MATCH($AA2&$C2,Sheet1!$B$4:$AT$4 & Sheet1!$B$7:$AT$7,0))

and down a row (which works as is at the moment):

=INDEX(Sheet1!$B18:$AT18,MATCH($AA3&$C3,Sheet1!$B$4:$AT$4 & Sheet1!$B$7:$AT$7,0))

I'm sure its a simple fix, but really struggling how to do it.

Thanks in advance!

 

[jarjarbingie]

Hi,

I have the following formula:

=INDEX(Sheet1!$B18:$AT18,MATCH($AA2&$C2,Sheet1!$B$4:$AT$4 & Sheet1!$B$7:$AT$7,0))

When I place it into next column, I want it to look like this:

=INDEX(Sheet1!$B19:$AT19,MATCH($AA2&$C2,Sheet1!$B$4:$AT$4 & Sheet1!$B$7:$AT$7,0))

and down a row (which works as is at the moment):

=INDEX(Sheet1!$B18:$AT18,MATCH($AA3&$C3,Sheet1!$B$4:$AT$4 & Sheet1!$B$7:$AT$7,0))

I'm sure its a simple fix, but really struggling how to do it.

Thanks in advance!

say the formula is in cell AC2:

AC2 = INDEX(OFFSET(Sheet1!$A$1,COLUMN()-11,1,1,45),MATCH($AA2&$C2,Sheet1!$B$4:$AT$4 & Sheet1!$B$7:$AT$7,0)) press CTRL+SHIFT+ENTER

what drives the change from 18 to 19 to the next column is COLUMN()-11

 

[BrianJN1]

In the first and third formulae you appear to wish to address the range B18:AT18 but the AA and C row indices have incremented.
In these instance you appear to need to preserve the B18:AT18 range, in which case you need that $ to precede each of the 18s.
For your second column do you need to do the same, based on my assumptions?

 

[alocke]

Thanks jarjarbinge! Works a treat!