Formula to pull list from one column based on data in multiple columns

[ltomlinson]

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]AUS
[/TD]
[TD]BOB
[/TD]
[TD]BOB
[/TD]
[TD]DREW
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HOU
[/TD]
[TD]DREW
[/TD]
[TD]BOB
[/TD]
[TD]BOB
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DFW
[/TD]
[TD]DREW
[/TD]
[TD]NANCY
[/TD]
[TD]DREW
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CRP
[/TD]
[TD]BOB
[/TD]
[TD]NANCY
[/TD]
[TD]NANCY
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAS
[/TD]
[TD]NANCY
[/TD]
[TD]DREW
[/TD]
[TD]DREW
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Hoping someone can help with this. I have a spreadsheet like this

I need t be ale to pull the list of data in column A based on a give name. For instance, If I type in Bob, I want the list to return AUS,HOU, and CRP as those are the only ones is name has ever been associated with.

Please help me out here!

 

[ltomlinson]

I apologize for the grammatical errors. I don't know why it typed out that way

 

[DRSteele]

You will need Excel 2016 for function TEXTJOIN to work.

Book1
	A	B	C	D	E	F	G
2	Code	Name1	Name2	Name3
3	AUS	BOB	BOB	DREW		Criteria	Code
4	HOU	DREW	BOB	BOB		BOB	AUS, HOU, CRP
5	DFW	DREW	NANCY	DREW
6	CRP	BOB	NANCY	NANCY
7	NAS	NANCY	DREW	DREW
Sheet21

Cell Formulas
Range	Formula
G4	{=TEXTJOIN(", ",1,IF(MMULT(--(B3:D7=F4),TRANSPOSE(COLUMN(B3:D3)^0)),A3:A7,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ltomlinson]

Is there any way to do it with Excel 2013? I do not have the Textjoin option.

 

[ltomlinson]

Even if it is a formula where I have to copy it down and get a unique distinct list, I am ok with that.

 

[DRSteele]

I don't think it's easy without TEXTJOIN. Sorry.

 

[Weazel]

maybe...

A9=INDEX($A$2:$A$6,SMALL(IF(FREQUENCY(IF($B$2:$G$6<>"",IF($B$2:$G$6=$A$8,MATCH($A$2:$A$6,$A$2:$A$6,0))),ROW($A$2:$A$6)-ROW($A$2)+1),ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($A$9:A9))) Control Shift Enter

could wrap it in an IFERROR to get rid of the #NUM errors and adjust your ranges

[TABLE="class: grid, width: 448"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]F[/TD]
[TD="width: 64"]G[/TD]
[/TR]
[TR]
[TD="width: 64"]1[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="width: 64"]2[/TD]
[TD="width: 64"]AUS[/TD]
[TD="width: 64"]BOB[/TD]
[TD="width: 64"]BOB[/TD]
[TD="width: 64"]DREW[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]HOU[/TD]
[TD]DREW[/TD]
[TD]BOB[/TD]
[TD]BOB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]DFW[/TD]
[TD]DREW[/TD]
[TD]NANCY[/TD]
[TD]DREW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]CRP[/TD]
[TD]BOB[/TD]
[TD]NANCY[/TD]
[TD]NANCY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]NAS[/TD]
[TD]NANCY[/TD]
[TD]DREW[/TD]
[TD]DREW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]nancy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]DFW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]CRP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]NAS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]#NUM ![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]#NUM ![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]#NUM ![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Using Weazel's layout, here's another option for the A9 formula:

=IFERROR(INDEX($A$2:$A$6,SMALL(IF(MMULT(--($B$2:$D$6=$A$8),TRANSPOSE(COLUMN($B$2:$D$2))),ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($A$9:$A9))),"")

with Control+Shift+Enter.

Or if you only intend to ever have 3 name columns:

=IFERROR(INDEX($A$2:$A$6,SMALL(IF(MMULT(--($B$2:$D$6=$A$8),{1;1;1}),ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($A$9:$A9))),"")

with CSE.