Formula or Macro that finds value and returns all sheet names that value is found in.

[hayblud]

Hi, all!

Hoping to find some help on this.

I have a data set where I need to find if values in column E in the "reference" sheet are found in my "sheet_list" (that contains all sheet names), then return all sheet names where that value is found in column E of those sheets.

For example:

E7 on "reference" sheet is found in 3 different sheets. Can I input a formula that will return all of 3 names of those sheets where E7 is found in column E of those specific sheets?

Apologies if this is not clear enough, but would greatly appreciate any help on this.

Thanks!

 

[p45cal]

See attached

 

[hayblud]

Thanks for the response. When I copy the formula over, it is just returning a blank.

=TEXTJOIN(", ",TRUE,BYROW(AC2:AC20,LAMBDA(sn,IF(ISNUMBER(MATCH(E6,INDIRECT(sn & "!E:E"),0)),sn,""))))
AC2:AC20 is the list of my sheet names.

 

[p45cal]

AC2:AC20 is on the same sheet as the formula?
Otherwise attach the file.
In the attached, is a version of the formula which caters for any space(s) in the sheet names.

 

[hayblud]

Disregard, I got it to work. Thank you so much for your help!

SOLVED!