Allow user input to change Table reference in Index, match, match

[Stevenb2020]

I have the following Index, match, match in my worksheet:

=-INDEX(Table2018PL[[#Headers],[#Data],[GlCode]:[4thQtr]],MATCH('FULL YEAR'!$A8,Table2018PL[[#Headers],[#Data],[GlCode]],0),MATCH('FULL YEAR'!E$5,Table2018PL[[#Headers],[GlCode]:[4thQtr]],0))

I would like to replace Table2018PL with Table2019PL using data from an input cell in another tab. The reference appears 3 times, once for array and twice for lookup array. Essentially what I want to do is make the "2018" variable based on user input.

Is there a way to do this?

Thanks,

 

[DanteAmor]

=-INDEX(INDIRECT("Table" & tab!B2 & "PL[[#Headers],[#Data],[GlCode]:[4thQtr]]"),MATCH('FULL YEAR'!$A8,INDIRECT("Table" tab!B2 & "PL[[#Headers],[#Data],[GlCode]]"),0),MATCH('FULL YEAR'!E$5,INDIRECT("Table" & tab!B2 & "PL[[#Headers],[GlCode]:[4thQtr]]"),0))


Change "tab" and "B2" by the name of your sheet and the cell where you will put the year

 

[Stevenb2020]

I have tried that and it still doesn't work. User input is into cell B10 on tab 'Set-up'. Here is what I am trying:

=-INDEX("Table"&'Set-up'!B10&"PL"[[#Headers],[#Data],[GlCode]:[4thQtr]],MATCH('FULL YEAR'!$A8,"Table"&'Set-up'!B10&"PL"[[#Headers],[#Data],[GlCode]],0),MATCH('FULL YEAR'!E$5,"Table"&'Set-up'!B10&"PL"[[#Headers],[GlCode]:[4thQtr]],0))

"PL"[[#Headers] is highlighted in grey and

The message I get is:

There's a problem with this formula.

Not trying to type a formula?
When the first character is an equal (=) of minus (-) sign, Excel thinks it's a formula:

you type: =1+1, cell shows: 2

To get around this type an apostrophe (') first:

you type: '=1+1, cell shows: =1+1

OK Help

 

[DanteAmor]

Check the correct position of the "

For example:

My formula

=-INDEX(INDIRECT("Table" & tab!B2 & "PL[[#Headers],[#Data],[GlCode]:[4thQtr]]")

Your formula

=-INDEX("Table"&'Set-up'!B10&"PL"[[#Headers],[#Data],[GlCode]:[4thQtr]]
Try this:

=-INDEX(INDIRECT("Table" & 'Set-up'!B10 & "PL[[#Headers],[#Data],[GlCode]:[4thQtr]]"),MATCH('FULL YEAR'!$A8,INDIRECT("Table" 'Set-up'!B10 & "PL[[#Headers],[#Data],[GlCode]]"),0),MATCH('FULL YEAR'!E$5,INDIRECT("Table" & 'Set-up'!B10 & "PL[[#Headers],[GlCode]:[4thQtr]]"),0))

 

[Stevenb2020]

That works! Great, thank you so much Dante! I missed the second "

 

[DanteAmor]

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