Function for an array
 

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?

Function for an array
 

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.

--
Biff
Microsoft Excel MVP


"KatiJ" wrote in message
...
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?

Function for an array
 

What add on is that?

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"KatiJ" wrote in message
...
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?

Function for an array
 

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.

--
Biff
Microsoft Excel MVP


"KatiJ" wrote in message
...
What add on is that?

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"KatiJ" wrote in message
...
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?