Lookup creteria match formula with results in rows (horizontal)

[Pi*]

Hi.

Is there a formula that can lookup a distinct creteria and return all values associated with this creteria? The trouble I am having is I want the results of the formula in a row (horizontal) not vertical. As an example I am looking for the unique values in column B that are associated with either creteria in column A.
I want the formula result to not repeat any value already found. Below is an example I have pasted;

Column A Column B


Row3 MX Beatle
Row4 AA Eel
Row5 EK Ant
Row6 OP Cobra
Row7 LD Centipede
Row8 AA Spider
Row9 LD Beatle
Row10 MX Mites
Row11 PP Eel
Row12 BB Sting
Row13 AA Spider
Row14 CP Crab
Row15 LD Crab
Row16 BB Ant
Row17 MX Cobra
Row18 LD Hyte

Lookup Formula results below

MX Beatle Mites Cobra #N/A
AA Eel Spider #N/A
LD Crab Centipede Beatle Hyte #N/A



Say If I am looking for all Values/Text associated with MX, the formula should return Beatle, Mites and Cobra. and so forth.

Your assistance will be much appreciated.

Pi*

[bebo021999]

Is it are you looking for?

[AlKey]

Here is a shorter formula
Enter in E3 copy across and down

***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

=IFERROR(INDEX($B:$B,SMALL(IF($A$3:$A$18=$D3,ROW($A$3:$A$18)),COLUMNS($A:A))),"")

[shukla.ankur281190]

or try a non array formula

Row\Col	A	B	C	D	E	F	G	H
3	MX	Beatle
4	AA	Eel
5	EK	Ant
6	OP	Cobra		MX	Beatle	Mites	Cobra
7	LD	Centipede		AA	Eel	Spider	Spider
8	AA	Spider
9	LD	Beatle
10	MX	Mites
11	PP	Eel
12	BB	Sting
13	AA	Spider
14	CP	Crab
15	LD	Crab
16	BB	Ant
17	MX	Cobra
18	LD	Hyte

E6

=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$3:$A$18)/($A$3:$A$18=$D6),COLUMNS($D6:D6))),"")

Copy across

[Pi*]

Good morning,

Thank you all sincerely, but is there a way the formula will not repeat a value already found more than once? For instance If I am searching for criteria associated with AA in my original list, I want the result of the formula to show Eel Spider only not Eel Spider Spider. I don't want the formula to repeat any values found twice.

I appreciate the help

[AlKey]

Try this
Enter in E3, copy across and down
Array formula must be entered with Ctrl+Shift+Enter key combination.

=IFERROR(INDEX($B$3:$B$18,MATCH(0,IF($A$3:$A$18=$D3,COUNTIF($D$3:D3,$B$3:$B$18)),0)),"")

v	A	B	C	D	E	F	G	H
1
2
3	MX	Beatle		MX	Beatle	Mites	Cobra
4	AA	Eel		AA	Eel	Spider
5	EK	Ant		EK	Ant
6	OP	Cobra		OP	Cobra
7	LD	Centipede		LD	Centipede	Crab	Hyte
8	AA	Spider		PP	Eel
9	LD	Beatle		BB	Sting
10	MX	Mites		CP	Crab
11	PP	Eel
12	BB	Sting
13	AA	Spider
14	CP	Crab
15	LD	Crab
16	BB	Ant
17	MX	Cobra
18	LD	Hyte
19

[Pi*]

Good evening,

Perfect! AlKey. Thank you very much for your time bebo021999 and shukla.ankur281190. Bless you all.

Pi*