Matching and Indexing

[vfaulkner]

Hello,

I'm have a table tracking relationships between Mentors and Mentees (see the attached table). I would like to input a mentee name in cell F2 and return everyone mentoring that person in the following column (G2 downward).

Any thoughts? I suspect it is a complicated series of matching and indexing.

[tigeravatar]

vfaulkner,

Attached is a modified version of your posted workbook. In cell G2 and copied down is this formula:

=IFERROR(INDEX($B$1:$D$1,MATCH(1,INDEX((COUNTIF($G$1:G1,$B$1:$D$1)=0)*(INDEX($B$2:$D$4,MATCH($F$2,$A$2:$A$4,0),0)="x"),),0)),"")

[vfaulkner]

Thank you! This is exactly what i was looking for.

Vfaulkner

[vfaulkner]

tigeravatar,

Do you know what the formula would be if I wanted to do the reverse lookup? IE look up the Mentee by Mentor?

[tigeravatar]

Where F2 is the name of the Mentor whose Mentees you're looking up:

=IFERROR(INDEX($A$2:$A$4,MATCH(1,INDEX((COUNTIF($G$1:G1,$A$2:$A$4)=0)*(INDEX($B$2:$D$4,0,MATCH($F$2,$B$1:$D$1,0))="x"),),0)),"")

[vfaulkner]

Thanks again!

[tigeravatar]

You're very welcome