Search range for spcific name in cells than in another cell output there placement-CodePudding

What I would like to do in my excel workbook is search for the entire sheet for the name sam. If sam is contained I would like the specific cell placement to be printed out in cell d12. So I assume cell d12 will have some kind of formula to do this. The photo below shows exactly what I am looking for.

CodePudding user response：

You can use a solution similar like this: =TEXTJOIN(",",1,IF($1:$10="a",ADDRESS(ROW($1:$10),COLUMN($1:$10)),""))

enter image description here

PS As you mention you want to search the whole sheet I recommend you to search from a different sheet to the data sheet, so you will not cope with circular reference.

To not show the $ in the result you could use: =TEXTJOIN(",",1,IF($1:$10="a",ADDRESS(ROW($1:$10),COLUMN($1:$10),4),""))

Alternate to volatile ADDRESS function that recalculates on each change you could use the following as suggested by Scott Craner: =TEXTJOIN(", ",TRUE,IF($C$2:$E$8="Sam",CHAR(COLUMN($C$2:$E$8) 64)&ROW($C$2:$E$8),"")). Note that if the column number of a matching value exceeds 27 it'll return wrong results.