List data in one column based on the criteria on another column

[rotmanite11]

Hi there,

I need to find names from one column based on a criteria on another column. I would normally do this using INDEX/MATCH function.

However, the challenge I have is that I need to list in order (doesn't have to be alphabetic) all the names that match the criteria. I have shared below a sample as example:


DATA:
[TABLE="width: 182"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Contact[/TD]
[/TR]
[TR]
[TD]Tim Lumber[/TD]
[TD]Simon Keith[/TD]
[/TR]
[TR]
[TD]JoeFish[/TD]
[TD]Phil Cornwall[/TD]
[/TR]
[TR]
[TD]Bill Café[/TD]
[TD]Jen Bass[/TD]
[/TR]
[TR]
[TD]Coffee Room[/TD]
[TD]Steven Rodes[/TD]
[/TR]
[TR]
[TD]BFT Shop[/TD]
[TD]Helen Mair[/TD]
[/TR]
[TR]
[TD]Fly Today[/TD]
[TD]Tony Pease[/TD]
[/TR]
[TR]
[TD]JoeFish[/TD]
[TD]Matt Homles[/TD]
[/TR]
[TR]
[TD]The Woodshop[/TD]
[TD]Greg Chaps[/TD]
[/TR]
[TR]
[TD]JoeFish[/TD]
[TD]Eaton Helm[/TD]
[/TR]
[TR]
[TD]King Palace[/TD]
[TD]Barney Coo[/TD]
[/TR]
[TR]
[TD]BFT Shop[/TD]
[TD]Ray Pardright[/TD]
[/TR]
[TR]
[TD]Coffee Room[/TD]
[TD]Sienna Wily[/TD]
[/TR]
[TR]
[TD]Big Catering[/TD]
[TD]Sarah Cooper[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OUTPUT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company[/TD]
[TD]Contact[/TD]
[/TR]
[TR]
[TD]Joe Fish[/TD]
[TD]Phil Cornwall[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Matt Homles[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Eaton Helm[/TD]
[/TR]
</tbody>[/TABLE]


I need to get the result as the output shown above. I know I might be able to do this using Filter/Sort function as well, but I am trying to do it using a formula.

Thanks for your help.

Cheers

 

[Momentman]

Drag the formula downwards from E1

Excel 2010
	A	B	C	D	E
1	Company	Contact		JoeFish	Simon Keith
2	Tim Lumber	Simon Keith			Tony Pease
3	JoeFish	Phil Cornwall			Greg Chaps
4	Bill Caf	Jen Bass
5	Coffee Room	Steven Rodes
6	BFT Shop	Helen Mair
7	Fly Today	Tony Pease
8	JoeFish	Matt Homles
9	The Woodshop	Greg Chaps
10	JoeFish	Eaton Helm
11	King Palace	Barney Coo
12	BFT Shop	Ray Pardright
13	Coffee Room	Sienna Wily
14	Big Catering	Sarah Cooper
Sheet6

Cell Formulas
Range	Formula
E1	{=IFERROR(INDEX($B$1:$B$14,SMALL(IF($A$2:$A$14=$D$1,ROW($A$2:$A$14)-ROW($A$2)+1),ROW(A1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[rotmanite11]

Thanks a lot. Works like a Charm!