2 way Xlookup multiple sheets with list

%3CLINGO-SUB%20id%3D%22lingo-sub-2836101%22%20slang%3D%22en-US%22%3E2%20way%20Xlookup%20multiple%20sheets%20with%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2836101%22%20slang%3D%22en-US%22%3EHi%20I'm%20lookining%20to%20do%20a%20two%20way%20lookup%20across%20multiple%20sheets%20using%20the%20list%20function.%3CBR%20%2F%3EEx%3CBR%20%2F%3EA1%20is%20the%20list%20of%20sheets%2C%3CBR%20%2F%3EColum%20b%3CBR%20%2F%3ERev%3CBR%20%2F%3EDirect%20costs%3CBR%20%2F%3ESga%3CBR%20%2F%3EEdit%3CBR%20%2F%3ERow%20A%3CBR%20%2F%3EJan-sept%3CBR%20%2F%3ESheets%3CBR%20%2F%3EDifferent%20areas%3CBR%20%2F%3ECalgary%3CBR%20%2F%3EEdmonton%3CBR%20%2F%3EVancouver%3CBR%20%2F%3EToronto%20ect..%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2836101%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2836266%22%20slang%3D%22en-US%22%3ERe%3A%202%20way%20Xlookup%20multiple%20sheets%20with%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2836266%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1180747%22%20target%3D%22_blank%22%3E%40Ntratechaud%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou've%20had%20quite%20a%20few%20%22views%22%20of%20this%20inquiry%20and%20as%20of%20this%20writing%20no%20replies.%20I%20suspect%20the%20reason%20is%20that%20it's%20not%20really%20clear%20what%20you're%20trying%20to%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%20a%20workbook%20that%20you've%20created%20in%20which%20you're%20trying%20to%20do%20this%20%22two-way%20lookup%22%3F%20So%20long%20as%20it%20contains%20no%20confidential%20or%20proprietary%20info%2C%20posting%20that%20workbook%20would%20be%20of%20far%20more%20value%2C%20to%20any%20of%20us%20that%20might%20be%20able%20to%20help%2C%20than%20a%20verbal%20description%2C%20and%20I%20mean%20the%20actual%20workbook%20(not%20just%20an%20image)......%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
Hi I'm lookining to do a two way lookup across multiple sheets using the list function.
Ex
A1 is the list of sheets,
Colum b
Rev
Direct costs
Sga
Edit
Row A
Jan-sept
Sheets
Different areas
Calgary
Edmonton
Vancouver
Toronto ect..
3 Replies

@Ntratechaud 

 

You've had quite a few "views" of this inquiry and as of this writing no replies. I suspect the reason is that it's not really clear what you're trying to do.

 

Do you have a workbook that you've created in which you're trying to do this "two-way lookup"? So long as it contains no confidential or proprietary info, posting that workbook would be of far more value, to any of us that might be able to help, than a verbal description, and I mean the actual workbook (not just an image)......

@mathetes 
thank you,
please see attached.

@Ntratechaud 

 

The INDIRECT function will serve you better. Here's the formula I entered into cell D4.

=INDIRECT($A$2&"!"&D$1&$A4)

 

The full file is attached. Here, though, is an image showing how it works when copied to all the adjacent cells. Notice the little "helpers" I entered in the top row and the side column. Those could be hidden in a fully functional workbook; they're visible here just for learning purposes.

mathetes_0-1634061727542.png