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:

Company	Contact
Tim Lumber	Simon Keith
JoeFish	Phil Cornwall
Bill Café	Jen Bass
Coffee Room	Steven Rodes
BFT Shop	Helen Mair
Fly Today	Tony Pease
JoeFish	Matt Homles
The Woodshop	Greg Chaps
JoeFish	Eaton Helm
King Palace	Barney Coo
BFT Shop	Ray Pardright
Coffee Room	Sienna Wily
Big Catering	Sarah Cooper
OUTPUT
Company	Contact
Joe Fish	Phil Cornwall
	Matt Homles
	Eaton Helm

<tbody>
</tbody>

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!