# INDIRECT formula with Relative Column References

#### pliskers

##### Active Member
I have a formula using the INDIRECT function to reference the name of the tab indicated in cell A11. This formula works and pulls in the indexed value in column G on that page. What I'd like to do is copy this formula across several columns and have the referenced index column change to H:H, I:I, etc.

Nothing I've tried has worked - is there any answer?

=INDEX(INDIRECT("'"&\$A11&"'!g:g"),MATCH(\$C\$8,INDIRECT("'"&\$A11&"'!C:C"),0))

Many thanks!

#### DanteAmor

##### Well-known Member
Try this

=INDEX(INDIRECT("'"&\$A11&"'!G:J"),MATCH(\$C\$8,INDIRECT("'"&\$A11&"'!C:C"),0),COLUMNAS(\$A\$1:A1))

#### pliskers

##### Active Member
Try this

=INDEX(INDIRECT("'"&\$A11&"'!G:J"),MATCH(\$C\$8,INDIRECT("'"&\$A11&"'!C:C"),0),COLUMNAS(\$A\$1:A1))
What does the COLUMNAS perform? By having the reference columns "G:J" within the INDIRECT formula in quotes, those column references will not change as I copy the formula across multiple columns.

#### DanteAmor

##### Well-known Member
What does the COLUMNAS perform? By having the reference columns "G:J" within the INDIRECT formula in quotes, those column references will not change as I copy the formula across multiple columns.

The G:J reference does not change. But the result does change. Because in the INDEX function you can indicate the return value, 1 for first column, 2 for the second column, 3 for the third column and so on.
You must change J for the last column you will use.

---------------------------------

COLUMNAS(\$A\$1:A1)
With this instruction you get the number 1, when you copy the formula across gets the number 2, 3, 4, and so on.

Then

=INDEX(INDIRECT("'"&\$A11&"'!G:J"),MATCH(\$C\$8,INDIRECT("'"&\$A11&"'!C:C"),0),COLUMNAS(\$A\$1:A1))
=INDEX(INDIRECT("'"&\$A11&"'!G:J"),MATCH(\$C\$8,INDIRECT("'"&\$A11&"'!C:C"),0),1)
=INDEX(gets result from G,MATCH(\$C\$8,INDIRECT("'"&\$A11&"'!C:C"),0),1)

Next:
=INDEX(INDIRECT("'"&\$A11&"'!G:J"),MATCH(\$C\$8,INDIRECT("'"&\$A11&"'!C:C"),0),COLUMNAS(\$A\$1:B1))
=INDEX(INDIRECT("'"&\$A11&"'!G:J"),MATCH(\$C\$8,INDIRECT("'"&\$A11&"'!C:C"),0),2)
=INDEX(gets result from H,MATCH(\$C\$8,INDIRECT("'"&\$A11&"'!C:C"),0),2)

Next:
=INDEX(INDIRECT("'"&\$A11&"'!G:J"),MATCH(\$C\$8,INDIRECT("'"&\$A11&"'!C:C"),0),COLUMNAS(\$A\$1:C1))
=INDEX(INDIRECT("'"&\$A11&"'!G:J"),MATCH(\$C\$8,INDIRECT("'"&\$A11&"'!C:C"),0),3)
=INDEX(gets result from I,MATCH(\$C\$8,INDIRECT("'"&\$A11&"'!C:C"),0),3)

#### pliskers

##### Active Member
What does the COLUMNAS perform? By having the reference columns "G:J" within the INDIRECT formula in quotes, those column references will not change as I copy the formula across multiple columns.
Very good, thank you!

• DanteAmor

#### Peter_SSs

##### MrExcel MVP, Moderator
If I have understood correctly then you could also swap to VLOOKUP, only requiring one INDIRECT function.

=VLOOKUP(\$C\$8,INDIRECT("'"&\$A11&"'!C:J"),4+COLUMNS(\$B11:B11),0)

Last edited: