INDIRECT formula with Relative Column References

pliskers

Active Member
Joined
Sep 26, 2002
Messages
388
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
Joined
Dec 3, 2018
Messages
7,810
Office Version
2007
Platform
Windows
Try this

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

pliskers

Active Member
Joined
Sep 26, 2002
Messages
388
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
Joined
Dec 3, 2018
Messages
7,810
Office Version
2007
Platform
Windows
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
Joined
Sep 26, 2002
Messages
388
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,810
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,755
Office Version
365
Platform
Windows
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:

Forum statistics

Threads
1,077,991
Messages
5,337,591
Members
399,156
Latest member
RaudMees

Some videos you may like

This Week's Hot Topics

Top