Excel formula: How to concatenate multiple match results addresses (instead of values)

Question

First of all thanks for reading my post and for trying to help me.

I would like to know if it is possible to match multiple values from a column and concatenate (separated by a comma for instance) their addresses instead of their values.

The picture shows an example of what I want to do. As looking for Dogs, I get the address of each occurrence instead of its value.

I have used both AGGREGATE and TEXTJOIN as well as INDEX and MATCH formulas but I do not know how to use these in order to get the addresses of the matches.

Any help is much appreciated.

Thanks.

Answer 1

You need to also use the ADDRESS function:

Note that the formula below will adjust for whatever column contains your animals list, in case it happens not be column A.

   =TEXTJOIN(",",TRUE,ADDRESS(AGGREGATE(15,6,1/(theAnimal=animals)*ROW(animals),ROW(INDIRECT("1:"&COUNTIF(animals,theAnimal)))),COLUMNS(animals),4))

Depending on your version of Excel, you may need to enter this as an array formula. To enter/confirm an array formula, hold down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.

Answer 2

Or using this array formula (Ctrl+Shift+Enter) without ADDRESS function :

=TEXTJOIN( ",",1,"A"&AGGREGATE(15,6,ROW(A1:A12)/(A1:A12="Dog"),ROW(INDIRECT("1:"&COUNTIF(A:A,"Dog")))))