Function for an array

[KatiJ]

I'm trying to figure out how to write a function (Let me explain it in an
example).

Lets say Column A is numbered from 1 - 20. In Column B there is a word that
could be one of four things (Apple, Bird, Cat, Dog). I am looking for a
function that will search for all Dogs and in another cell, write down the
number in Column A that corresponds with it.

Column A Column B
1 Cat
2 Dog
3 Apple
4 Dog
5 Bird

So in a Cell that i choose, it will list {2, 4}. Is this even possible to
do with a function?

 

[T. Valko]

There's no *practical* way to do it the way you want with the built-in
functions.

You could do it with the built-in functions if you return the results to
individual cells. There is a free add-in available that will do it the way
you want it with the limitation that the resulting string can't be more that
255 characters.

 

[KatiJ]

What add on is that?

There's no *practical* way to do it the way you want with the built-in
functions.

You could do it with the built-in functions if you return the results to
individual cells. There is a free add-in available that will do it the way
you want it with the limitation that the resulting string can't be more that
255 characters.

 

[T. Valko]

See this:

http://xcell05.free.fr/morefunc/english/index.htm

The function you need is called MCONCAT.

You would use it in an array formula** like this:

=SUBSTITUTE(TRIM(MCONCAT(IF(B1:B20="dog",A1:A20,"")&" "))," ",",")

The result would be: 2,4

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.