INDIRECT formula with Relative Column References

[pliskers]

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]

Try this

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

 

[pliskers]

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]

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]

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]

I'm glad to help you. Thanks for the feedback.

 

[Peter_SSs]

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)