Looking up a data point in another workbook and returning the name of the sheet

[SKatz]

Hello,
Is there a formula that will do a lookup in another workbook for a data point (in this case, a unique email address) and return the sheet name where it is found?

Unfortunately, the workbooks cannot be merged in anyway as they are both being added to on a daily basis by separate teams so I can't copy the sheet with the email address list in to the workbook where I want to find them.

We don't need to pull any data from the row where the email address is found; we only need the name of the sheet where the email address is found.

Thank you!

 

[mumps]

I'm not sure if a formula can do what you want. If you are open to using a macro, that should work. Do you always want to do the "lookup" in the same workbook? Is the desired email to be found always the same?

 

[SKatz]

Hello! Yes the lookup is always in the same workbook, but it will be different email addresses I need to find across all the sheets and have the sheet name returned. The sheet names in the workbook are static if that helps. I can get the macro as far as the lookup but not sure how to record the find and return of the sheet name.

 

[mumps]

Are the email addresses to be found listed anywhere in the workbook where the sheet names are to be returned? If so, in which sheet and range are they located?

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Lookup formula that returns the sheet name in another workbook
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.