Using a formula in VLOOKUP table to look up a different tab or book

[ReggaeYT]

Hello all,

So I will do my best to explain this convoluted scenario: I'd like to make a standard VLOOKUP formula that can look at different tabs or books. So let's say there are 3 people: Brian, Daniel, and Kyle. Each one has a spreadsheet of their own called <name>_book (Brian_tab, Daniel_tab, Kyle_tab). The A column of my spreadsheet would have their names written in them, image attached.

Is there anyway to make the VLOOKUP formula include the names listed in the A cell, when looking up the tab?

=VLOOKUP(A2, A2&"_tab"!A:B, 2, 0)

which I hope would return:

=VLOOKUP(Brian, Brian_tab!A:B, 2, 0)

But when I try this, Excel returns #N/A each time. Is there a way to do this or am I just pissing in the wind?

Thanks!

 

[Sufiyan97]

Try

=VLOOKUP(A2,INDIRECT(A2&"!A:B",TRUE),2,0)