Help with Search/Lookup Functions

Hey all,

I'm building a spreadsheet for my boss and I've run into a bit of a pickle. The purpose of the spreadsheet is to track Current Mentor/Protege/Past Mentor relationships between workers. On one sheet (labeled 'Mentor - Protege') I have a list of names running from C:C, then the same names in a different order running from 2:2. When people give me their mentors/proteges, I find their name in C:C, then run across and find their mentor or protege's name in 2:2, putting either a "PM" (past mentor) "P" (protege) or "CM" (current mentor) in the corresponding box. On a separate sheet, ("Sheet1") I have the same list of names in B:B. I'd like to be able to put a function in column C that matches the name in column B on Sheet1 to the corresponding name on the 'Mentor - Protege' sheet in column C:C, then runs across and when it finds a box with with either "PM", "P" or "CM", it spits out the name directly above, in 2:2. The twist is that there are multiple entries, so I need to be able to drag the formula across vertically so that it grabs each entry until I get an error.

It's a bit difficult to comprehend, I attached a sample spreadsheet FYI.

Thanks!

HI eschaye,

When people give me their mentors/proteges, I find their name in C:C,

this range has only one entry i.e, Doe, John... suggest you to revisit your post. thanks.

Regards,
DILIPandey
<click on below * if this helps>