Dynamic Vlookup with usage of indirect

Question

I'm having trouble with the usage of Indirect function.

Here's what i'm looking for, I'm trying to create a dynamic vlookup based on the current tab.

=VLOOKUP(B3;'NH BBC'!$E$1:$Z$188;MATCH("Share Outstanding";'NH BBC'!$E$1:$Z$1;0);0)

My plan is to modify the 'NH BBC' by 'NH ' & RIGHT(CELL("filename");3) Supposing that the name of my tab is XXX_BBC.

I've tried to use indirect function but I'm not sure I'm on the good way.

Here's what I've tried:

=VLOOKUP(B3;INDIRECT("'" "NH " & "RIGHT(CELL("'" & "filename" & "'" & ");3)" & "!" & "E1:Z188");MATCH("Share Outstanding";'NH BBC'!$E$1:$Z$1;0);0)

Hope I've been clear.

Thanks in advance !

Answer 1

I've finally found and this formula is working perfectly.

VLOOKUP($B3;INDIRECT("'NH "&RIGHT(CELL("filename");3)&"'!$G$1:$ZZ$9999");MATCH("SHARE_OUTSTANDING";INDIRECT("'NH "&RIGHT(CELL("filename");3)&"'!$G$1:$ZZ$1");0))

By the way the issue i've got is that the cell are changing when i'm using the formula in another tab. Is this possible to look the value i've obtained ?

Something like a F9 ?

Answer 2

You are trying to concatenate some text with the results returned from a formula, but you are sticking the formulas in quotes, turning them into text. Furthermore, you are not keeping very good track of your text. There are quotes all over the place. Take this bit by bit in a seperate cell if need, slowly growing your formula from the inside out so you can insure everything is as expected. Right now it's a mess.

INDIRECT("'" "NH " & "RIGHT(CELL("'" & "filename" & "'" & ");3)" & "!" & "E1:Z188")

Should be:

 INDIRECT("'NH " & RIGHT(CELL("filename");3) & "'!E1:Z188")

No need for all the complication.