Allocating Mentors to Students based on criteria

Georgieee_xo

New Member
Joined
Aug 8, 2018
Messages
1
Hi,

I am trying to find a formula which enables me to match students with a mentor on the basis of 'area of interest' and 'location'.

I have a spreadsheet with student and mentor data in separate sheets (approx. 150 students and 150 mentors). I have added a 'helper' column, and used a VLOOKUP to allocate mentors, based on the criteria, which works with a small population, but I have run into a major problem when applying it over 150 pairs. I want to allocate each mentor to only one student. At the moment, students with the same criteria are being allocated the same mentor (first one to appear on the mentor list who fits with the criteria). For example, John Smith (mentor) will be allocated to all students who match his criteria. Is there a way to get the formula to skip to the next mentor (who fits the criteria) if the previous mentor has already been allocated to a student?

I basically want to do the VLOOKUP + if mentor is already allocated, skip to next match.

Student Data (example)

First NameSurnameArea of interestLocationMentor
JoeBloggsFinanceLondon*VLOOKUP result*
Tom Jones
JaneDoeLawBirmingham*VLOOKUP result*
Amy Adams
RyanDaviesPoliticsLondon*VLOOKUP result*
John Smith
JohnDoeMedicineCardiff*VLOOKUP result*
Julia Roberts
EmilySmithPoliticsLondon*VLOOKUP result*
John Smith**

<tbody>
</tbody>

**should be Kate Moss as John Smith has already been allocated

Mentor Data (example)

Helper
First Name
Surname
Practice Area
Location
PoliticsLondonJohnSmithPoliticsLondon
FinanceLondonTomJonesFinanceLondon
LawBirminghamAmyAdamsLawBirmingham
MedicineCardiffJuliaRobertsMedicineCardiff
PoliticsLondonKateMossPoliticsLondon

<tbody>
</tbody>

I hope that makes sense! Any help would be greatly appreciated.

Thank you :)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,309
Office Version
365
Platform
Windows
Welcome to the MrExcel board!

I would add another helper column to list full name. Then use this, copied down

Excel Workbook
ABCDEF
1First NameSurnameArea of interestLocationMentor
2JoeBloggsFinanceLondon
3JaneDoeLawBirminghamAmy Adams
4RyanDaviesPoliticsLondonJohn Smith
5JohnDoeMedicineCardiffJulia Roberts
6EmilySmithPoliticsLondonKate Moss
7AnnSmithPoliticsLondonNone Available
8
9
10HelperFirst NameSurnamePractice AreaLocationFull Name
11PoliticsLondonJohnSmithPoliticsLondonJohn Smith
12FinanceLondonTomJonesFinanceLondonTom Jones
13LawBirminghamAmyAdamsLawBirminghamAmy Adams
14MedicineCardiffJuliaRobertsMedicineCardiffJulia Roberts
15PoliticsLondonKateMossPoliticsLondonKate Moss
Mentor Allocation



The formula could be marginally simpler if you also added a helper column to the top table

Excel Workbook
ABCDEF
1First NameSurnameArea of interestLocationMentorArea/Location
2JoeBloggsFinanceLondonFinanceLondon
3JaneDoeLawBirminghamAmy AdamsLawBirmingham
4RyanDaviesPoliticsLondonJohn SmithPoliticsLondon
5JohnDoeMedicineCardiffJulia RobertsMedicineCardiff
6EmilySmithPoliticsLondonKate MossPoliticsLondon
7AnnSmithPoliticsLondonNone AvailablePoliticsLondon
8
9
10HelperFirst NameSurnamePractice AreaLocationFull Name
11PoliticsLondonJohnSmithPoliticsLondonJohn Smith
12FinanceLondonTomJonesFinanceLondonTom Jones
13LawBirminghamAmyAdamsLawBirminghamAmy Adams
14MedicineCardiffJuliaRobertsMedicineCardiffJulia Roberts
15PoliticsLondonKateMossPoliticsLondonKate Moss
Mentor Allocation (2)
 
Last edited:

Forum statistics

Threads
1,082,478
Messages
5,365,792
Members
400,852
Latest member
katedun

Some videos you may like

This Week's Hot Topics

Top