How to search other sheets for matching values and return a specific value

[myoung5149]

Hello!

Suppose I have 4 sheets in a workbook. I have a list of email addresses in the sheet titled "Email Master List". I would like to search all other sheets, titled "Learning Cohort 1", "Learning Cohort 2", and "Learning Cohort 3", for a specific email address. If the email address is found in any of those 3 sheets, I would like the sheet name to appear in a selected cell in "Email Master List".

Is this possible? If the sheet name is not possible, could I at least have it return with a specific value (i.e. "yes" or "no")

I appreciate your help!

Mike

[NickyC]

Hi
this macro lets you put a text string (eg an email address) into an input box and then searches for that string in the three specified sheets. If it finds the string it returns the name of the sheet where it was found in the active cell (in your example, I assume this will be in the master list sheet). Is that what you want? it will only return one sheet name if the address is on more than one sheet, and does not check to see if you are running the macro from the master sheet, but could easily be tweaked to do that if you like.

Please Login or Register  to view this content.

[QSGuy]

see attached formula solution

This involves creating a Named Range for the three Cohort spreadsheets - see new separate sheet named Cohort Names (see Name Manager in the Formulas tab for the what the range looks like and how to edit it to go into your workbook)

The Named Range then treats all the spreadsheets included under the Named Range as one - and an "array formula" is then used to search the range

The MATCH formula returns the sheet number (in the order of your sheet names in the new Corhort Names worksheet) of the Named Range your match is on - and then the CHOOSE function follows with the the 3 names of your worksheets. So, the Match formula result is either 1, 2 or 3 in this case and the choose function allows us to enter your sheet names to display according to the number in Match returns.

An array formula requires that you press CONTROL-SHIFT-ENTER rather than just "enter" when you have finished creating the formula otherwise you will get a @N/a response if you just clicked enter. You know you have done an array formula correcty if you see curly brackets {} once you press C+S+E

[maniacb]

Here is another solution for you

Please Login or Register  to view this content.