Function for an array

  • Thread starter Thread starter KatiJ
  • Start date Start date
K

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?
 
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.
 
What add on is that?

T. Valko said:
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.
Click to expand...
 
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.
 
Back
Top