lookup problem

[sony]

Hi
I want to lookup all the mail address and put them all to the column next to the Name , what formula i can use to achieve that? thanks in advance.

Book14
	A	B	C	D	E	F
2	Name	Email		Name	Email
3	A			A	mail1
4	B			A	mail2
5	C			B	mail3
6				B	mail4
7				B	mail5
8				C	mail6
9				C	mail7
10				C	mail8
11				C	mail9
12
13	Name	Email
14	A	mail1,mail2
15	B	mail3,mail4,mail5
16	C
17
Sheet1

 

[royUK]

Have you more than one email address per name?.
This message was edited by royUK on 2002-10-01 02:13

 

[Aladin Akyurek]

How about extracting mail-adressess to a horizontal range instead of a vertical range?

See...

Book2
	A	B	C	D	E	F
1			1	2	3	4
2	A	mail1,mail2	mail1	mail2
3	B	mail1,mail2,mail3	mail1	mail2	mail3
Sheet1

The formula in C2 (copiable across then down)...

=WMID($B2,C$1,1,",")

contains C$1 as index variable.

WMID is part of the morefunc.xll add-in, downloadable from:

http://longre.free.fr/english/index.html