I need to search all worksheets for the values in Worksheet one column A. The behavior should be similar to a CTRL-F find all selection. In A:1 of every worksheet is a name and if the value from column A is in that worksheet then A:1 will be returned. I do not need VLookup or HLookup. It might be doable with index and search combo, but I am not finding a good way to do that. I know I need an array search of some sort since I need to search everywhere. I have a solution that does not scale and is sloppy on the return. This is the formula I am currently using.

Column A is where the search values are pasted. Columns B-Z or however far is needed get the formula pasted in the first 200 rows which is the limit of the allowed search terms.

```
{=IF(OR($A2<>""),IF(OR($A2=Sheet26!$A$1:SZ$25000),Sheet26!A$1,"Not Found"),"")}

```

That is the formula for column Z and the sheet numbers are changed for each column that has a sheet. What I need to adjust this to is only having the formula in column B and it returning a concatenated value of all the names it found. There are lots of questions dealing with just one value or one range like this [EXCEL: Need to find a value in a range of cells from another worksheet and return value from adjacent cell](https://stackoverflow.com/questions/16308329/excel-need-to-find-a-value-in-a-range-of-cells-from-another-worksheet-and-retur) but nothing that actually answers what I need.

Currently the result I get is something like this.

```
A           B            C          D             E         ...
Star        Bob        Not Found     Ann          Not Found
Light       Bob         Jill          Not Found   Not Found
378         Not Found     Jill        Not Found   Not Found

```

What I would like to have is this

```
A          B         
Star       Bob, Ann
Light      Bob, Jill
378        Jill

```

How can I modify my formula to accomplish that?

Thanks