Hi,

Illustrative example of my problem:

I have a list of numbers and letters. How can I lookup the value " 1 " and
return the multiple results of z, a, g ? I want to type only one formula
in one cell, although I don't mind the result being spread out over multiple
cells.
Thanks for your thoughts on this.

1 z
2 b
1 a
3 e
4 f
1 g

One way...

Download and install the *free* add-in Morefunc.dll from:

http://xcell05.free.fr/english/

Then you could use this array formula** :

=SUBSTITUTE(TRIM(MCONCAT(IF(A1:A6=1,B1:B6,"")&" "))," ",",")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"ORLANDO V" wrote in message
...
Hi,

Illustrative example of my problem:

I have a list of numbers and letters. How can I lookup the value " 1 "
and
return the multiple results of z, a, g ? I want to type only one
formula
in one cell, although I don't mind the result being spread out over
multiple
cells.
Thanks for your thoughts on this.

1 z
2 b
1 a
3 e
4 f
1 g

ORLANDO V wrote:
Hi,

Illustrative example of my problem:

I have a list of numbers and letters. How can I lookup the value " 1 " and
return the multiple results of z, a, g ? I want to type only one formula
in one cell, although I don't mind the result being spread out over multiple
cells.
Thanks for your thoughts on this.

1 z
2 b
1 a
3 e
4 f
1 g


If the functions in the freely downloadable file at
http://home.pacbell.nete/beban are available to your workbook

=VLookups(1, A1:B6, 2) array entered into a vertical range of enough
rows to accommodate the output;

Or

=INDEX(VLookups(1, A$1:B$6, 2),ROW(1:1),1) entered into a cell and
filled down as far as required.

Alan Beban

Typo:

Download and install the *free* add-in Morefunc.dll from:

Should be:

Download and install the *free* add-in Morefunc.xll from:


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
One way...

Download and install the *free* add-in Morefunc.dll from:

http://xcell05.free.fr/english/

Then you could use this array formula** :

=SUBSTITUTE(TRIM(MCONCAT(IF(A1:A6=1,B1:B6,"")&" "))," ",",")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"ORLANDO V" wrote in message
...
Hi,

Illustrative example of my problem:

I have a list of numbers and letters. How can I lookup the value " 1 "
and
return the multiple results of z, a, g ? I want to type only one
formula
in one cell, although I don't mind the result being spread out over
multiple
cells.
Thanks for your thoughts on this.

1 z
2 b
1 a
3 e
4 f
1 g