Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

vlookup with sheet name

0

Attaching the excel sheet that I'm currently working on wherein I have added 3 sheets out of which the first one is the content sheet and the other two are ABCD and MMM respectively.While trying to fetch the data from content sheet to the rest, I've need to type sheet name manually in vlookup formula which apparently would be difficult to apply if there are more sheets.

Pls provide solution for this.

excel
MS Office Version: 16
posted by: Mannu (rep: 2) on Wed Mar 6, 2019 at 1:12 am
Answer
Discuss

Answers

0

Try this formula.

=VLOOKUP(MID(CELL("filename"),FIND("]",CELL("filename"))+1,255),Control!A:B,2,0)
posted by: Variatus (rep: 4889) on Wed Mar 6, 2019 at 1:46 am
Discuss

Discussion

Can we use indirect option in this
Mannu (rep: 2) Mar 6, '19 at 2:16 am
The sheet name can only be extracted from CELL("filename"). Once extracted, it can be used in many ways, including the INDIRECT function. From the single line of data in your sample workbook no system can be discerned. I am unable to say whether the VLOOKUP function you proposed and I complemented is the best way or perhaps another function, perhaps one involving INDIRECT, might be more appropriate or more efficient.
Variatus (rep: 4889) Mar 6, '19 at 8:25 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login