Get Address of Cell where Match was found

[McCITech]

Good afternoon!

I'm attempting to create a formula that will tell me the location where specific data was found.

The issue: There are 52 worksheets, and data can be found on more than 1 sheet.

So far, I have been successful in Counting the number of times the specific data was found using

=SUMPRODUCT(COUNTIF(INDIRECT("'" & Sheets & "'!B8:F8"),"Vacation"))

Where "Sheets" is a named range containing the names for all 52 tabs.

What I can't figure out is how to get the Address for each instance where the data is found.
I have tried using INDEX, ADDRESS, INDIRECT & MATCH in various combinations.

If anyone can help me out with this that would be great!
The end result of this formula would be (hopefully): Worksheet name(s) & Cell reference (A1) if that's even possible.

If more information is needed, please let me know and I will gladly supply it!

Thank you in advance for your assistance.

 

[Aladin Akyurek]

Control+shift+enter, not just enter, say, in A1 of Summary...

=IFERROR(INDEX(Sheets,SMALL(IF(ISNUMBER(1/COUNTIFS(INDIRECT("'"&Sheets&"'!B8:F8"),"vacation")),ROW(INDIRECT("1:"&COUNTIFS(Sheets,"?*")))),COLUMNS($A:A))),"")

This deliver the names of the sheets where "vaction" occurs at least once.

In A2 control+shift+enter, not just enter, and copy across:

=CELL("address",INDEX(INDIRECT("'"&A$1&"'!B8:F8"),MATCH("vacation",INDIRECT("'"&A$1&"'!B8:F8"),0)))

 

[McCITech]

Aladin,

This is great thank you!

I evaluated this formula and I see that it finds all the sheets that have 'vacation' at least once, but the formula only Displays the 1st instance.

Is there any way to expand this formula to output a list of sheets?

Thanks again for helping me with this.

 

[Aladin Akyurek]

Aladin,

This is great thank you!

I evaluated this formula and I see that it finds all the sheets that have 'vacation' at least once, but the formula only Displays the 1st instance.

Is there any way to expand this formula to output a list of sheets?

Thanks again for helping me with this.

You need to copy the formula across from A1 on rightwise. The same way for the formula in A2...

 

[McCITech]

Ahhhhhh!!

That is awesome!
Thank you so very much!

 

[Aladin Akyurek]

Ahhhhhh!!

That is awesome!
Thank you so very much!

You are welcome.