excel - Dynamic Vlookup with usage of indirect -

excel - Dynamic Vlookup with usage of indirect -

i'm having problem usage of indirect function.

here's i'm looking for, i'm trying create dynamic vlookup based on current tab.

=vlookup(b3;'nh bbc'!$e$1:$z$188;match("share outstanding";'nh bbc'!$e$1:$z$1;0);0)

my plan modify 'nh bbc' 'nh ' & right(cell("filename");3) supposing name of tab xxx_bbc.

i've tried utilize indirect function i'm not sure i'm on way.

here's 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 !

you trying concatenate text results returned formula, sticking formulas in quotes, turning them text. furthermore, not keeping track of text. there quotes on place. take bit bit in seperate cell if need, growing formula within out can insure expected. right it's mess.

indirect("'" "nh " & "right(cell("'" & "filename" & "'" & ");3)" & "!" & "e1:z188")

should be:

indirect("'nh " & right(cell("filename");3) & "'!e1:z188")

no need complication.

excel excel-formula vlookup