Vlookup on multiple sheets

Hi and welcome to the forum

Change your formula to this...
=VLOOKUP(G$5,INDIRECT(LEFT(G5,2)&"!D2:Q10"),2,0)

Thanks. The indirect() function is ideal for this sort of thing

And what would be the best alternative for an indirect function between two workbooks? I have been using the code below but it is slow.

Please Login or Register  to view this content.

Dear Savannaexcel,
Thank you very much. your formulae worked.but only for 7 sheets
my 50% problem solved.

Dear FDibbins,
yours is Great it really works............Thanks a lot, and thanks for explaining syntax.
100% solved

savanna, please use code tags when posting VBA (see my note 1 below).

Also, indirect() doesnt work between workbooks/files unless bot are open, you need an add-in for that (more_func i think its called)

roofi, the indirect() function is a means of taking text, and converting into something that excel can use as a range. So...
=VLOOKUP(G$5,INDIRECT(LEFT(G5,2)&"!D2:Q10"),2,0)
is taking the left 2 characters of the contents of G5 (FB or MR etc), and then combing that with your range D2:Q10 to give you the sheetname and cell range FB!D2:Q10. You can add as many sheets as you want, as long as you keep to 2 characters for the name. If you want to use more, then you can change the
LEFT(G5,2)
to LEFT(G5,search("=",G5,1)-1)
That will allow for more than 2 characters

Dear FDibbins,
yours is Great it really works............Thanks a lot, and thanks for explaining syntax.
100% solved