Search in either of two columns and return multiple results

[mackensteff]

Hello,

I have a feeling this should be easy, but in essence, I am trying to generate the bottom table using the top table. In the bottom table, I want to search by the name in the Mentor Row, using the top table Mentor 1 and Mentor 2 as the matching array, and return the mentees from the left-most column in the bottom table under the appropriate name. A bonus would be to create the Mentors row of names in the bottom table by pulling a unique list from both the Mentor 1 and Mentor 2 columns in the top table.

Thanks for taking a look!

Mentee	Discipline	Position	Mentor 1	Mentor 2
Jack Dodd	Chem	TT	Todd Waxman	Nate Smith
Clark Hill	Chem	TT	Ryan Hansen	Henry Claude
Becky Jensen	Chem	NTT	Nate Smith	Trish Stevens
Jim Lund	Phys	TT	Zane Bishop	Cade Potter
Beth Bond	Chem	TT	Ryan Hansen	Albert Parsons
Tim Randall	Chem	TT	Ben Haversham	Henry Claude
Trish Stevens	Chem	NTT	Ben Haversham	David Rossman
Kyle Wright	Phys	TT	Ben Haversham	Albert Parsons
Gale Johnson	Chem	TT	David Rossman	Cade Potter
Mentors	Ryan Hansen	Ben Haversham	Albert Parsons	Henry Claude	Todd Waxman	Nate Smith	David Rossman	Cade Potter	Zane Bishop	Trish Stevens
Mentees	Clark Hill	Tim Randall	Beth Bond	Clark Hill	Jack Dodd	Jack Dodd	Trish Stevens	Jim Lund	Jim Lund	Becky Jensen
	Beth Bond	Kyle Wright	Kyle Wright	Tim Randall		Becky Jensen	Gale Johnson	Gale Johnson
		Trish Stevens

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[mackensteff]

Thanks for the suggestion. I am on MacOS Catalina using Version 16.52 with a Microsoft 365 subscription.

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Mentee	Discipline	Position	Mentor 1	Mentor 2
2	Jack Dodd	Chem	TT	Todd Waxman	Nate Smith
3	Clark Hill	Chem	TT	Ryan Hansen	Henry Claude
4	Becky Jensen	Chem	NTT	Nate Smith	Trish Stevens
5	Jim Lund	Phys	TT	Zane Bishop	Cade Potter
6	Beth Bond	Chem	TT	Ryan Hansen	Albert Parsons
7	Tim Randall	Chem	TT	Ben Haversham	Henry Claude
8	Trish Stevens	Chem	NTT	Ben Haversham	David Rossman
9	Kyle Wright	Phys	TT	Ben Haversham	Albert Parsons
10	Gale Johnson	Chem	TT	David Rossman	Cade Potter
11
12	Mentors	Ryan Hansen	Ben Haversham	Albert Parsons	Henry Claude	Todd Waxman	Nate Smith	David Rossman	Cade Potter	Zane Bishop	Trish Stevens
13		Clark Hill	Tim Randall	Beth Bond	Clark Hill	Jack Dodd	Jack Dodd	Trish Stevens	Jim Lund	Jim Lund	Becky Jensen
14		Beth Bond	Trish Stevens	Kyle Wright	Tim Randall		Becky Jensen	Gale Johnson	Gale Johnson
15			Kyle Wright
Lists

Cell Formulas
Range	Formula
B13:B14,G13:I14,F13,J13:K13,D13:E14,C13:C15	B13	=FILTER($A$2:$A$10,($D$2:$D$10=B12)+($E$2:$E$10=B12))
Dynamic array formulas.

 

[mackensteff]

This works great to get the first row (13 in your example), but when I put it in row 14 of your example it just repeats the name, and doesn't select the next result.

 

[Fluff]

Just put the formula in B14 (with my example) & drag across, do not drag down.