How to extract multiple matches into separate columns in different sheet?

[Vikranth]

Hi All,

I am new to this forum & this is my first Q.

We have 2 sheets. Sheet1 has Column A & B. Column A has a unique area name from A2: A100 and column B has our executive names from B2: B100, Each executive can have multiple areas. We have 10 executives.

What I Need is...

In Sheet2 I have all the executive names in a horizontal way, Ie: A1 with first executive name, B1 with second executive name, C1 with third executive name and so on till J1 with the tenth executive name.
I want the area names assigned to then under each executive name.

Example - In the sheet2 cell, A1 will have First Executive Name and under them, all the 10 areas list need to appear from Sheet1 column A

Can someone please help me with the formula ASAP?

 

[Peter_SSs]

I am new to this forum & this is my first Q.

Interesting. Who joined in 2013 and asked this question then?

 

[Legacy 143009]

Paste the following Sheet2 A1 and copy to right:

=INDEX(Sheet1!$A$2:$B$200,MATCH(E$1,Sheet1!$B$2:$B$200,0),1)

 

[Peter_SSs]

See if this smaller example is doing what you want.

Excel Workbook
	A	B
1	Area	Exec
2	Area 1	Exec 1
3	Area 2	Exec 2
4	Area 3	Exec 2
5	Area 4	Exec 3
6	Area 5	Exec 1
7	Area 6	Exec 2
8
Sheet1

Formula in A2 is copied across and down.

Excel Workbook
	A	B	C
1	Exec 1	Exec 2	Exec 3
2	Area 1	Area 2	Area 4
3	Area 5	Area 3
4		Area 6
5
Sheet2

 

[Legacy 143009]

Paste the following Sheet2 A1 and copy to right:

=INDEX(Sheet1!$A$2:$B$200,MATCH(E$1,Sheet1!$B$2:$B$200,0),1)

Completely sorry for my formula. I misread the question, I also tought the executives are unique..

 

[Vikranth]

Interesting. Who joined in 2013 and asked this question then?

Ha Ha Ha... I meant to say it's my first post...Thanks for the help.

 

[Peter_SSs]

Thanks for the help.

You're welcome.