Index and Match Variable Assistance

[Olivejuicecreative]

Using the index and match functionality in Excel, I am trying to append a text string to the front of the index reference.

Currently, I am manually inserting this "variable" in the file name manually. What I would like it to do is in the Table Name referenced below, replace the BK portion of the table name with the value listed in another column in the sheet.

I tried to create this dynamic table name with both "&" as well as a concantenate function but was unable to get it to function properly.

This is the syntax that is working WITHOUT a string being passed:

=INDEX(Table_BK_Solar_3_Pipeline[[#All],[Current Stage]],MATCH($A4,Table_BK_Solar_3_Pipeline[[#All],[LID]],0))

This is the way I would like the table name to be created:

=CONCATENATE("Table_",PMStatusRollup[[#This Row],[PM]],"_Solar_3_Pipeline")

Table_BK_Solar_3_Pipeline

<colgroup><col width="578"></colgroup><tbody>
</tbody>

or
="Table_"&PMStatusRollup[[#This Row],[PM]]&"_Solar_3_Pipeline"

Table_BK_Solar_3_Pipeline

<colgroup><col width="578"></colgroup><tbody>
</tbody>

Any help you can provide, would be greatly appreciated.

Cordially,

Amy Goldstein

 

[CharlesChuckieCharles]

Can you have dynamic tables names?