# Allocating Mentors to Students based on criteria

#### Georgieee_xo

##### New Member
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 Name Surname Area of interest Location Mentor Joe Bloggs Finance London *VLOOKUP result* Tom Jones Jane Doe Law Birmingham *VLOOKUP result* Amy Adams Ryan Davies Politics London *VLOOKUP result* John Smith John Doe Medicine Cardiff *VLOOKUP result* Julia Roberts Emily Smith Politics London *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 PoliticsLondon John Smith Politics London FinanceLondon Tom Jones Finance London LawBirmingham Amy Adams Law Birmingham MedicineCardiff Julia Roberts Medicine Cardiff PoliticsLondon Kate Moss Politics London

<tbody>
</tbody>

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

Thank you

#### Peter_SSs

##### MrExcel MVP, Moderator
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
4RyanDaviesPoliticsLondonJohn Smith
5JohnDoeMedicineCardiffJulia Roberts
6EmilySmithPoliticsLondonKate Moss
7AnnSmithPoliticsLondonNone Available
8
9
10HelperFirst NameSurnamePractice AreaLocationFull Name
11PoliticsLondonJohnSmithPoliticsLondonJohn Smith
12FinanceLondonTomJonesFinanceLondonTom Jones
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
4RyanDaviesPoliticsLondonJohn SmithPoliticsLondon
5JohnDoeMedicineCardiffJulia RobertsMedicineCardiff
6EmilySmithPoliticsLondonKate MossPoliticsLondon
7AnnSmithPoliticsLondonNone AvailablePoliticsLondon
8
9
10HelperFirst NameSurnamePractice AreaLocationFull Name
11PoliticsLondonJohnSmithPoliticsLondonJohn Smith
12FinanceLondonTomJonesFinanceLondonTom Jones
14MedicineCardiffJuliaRobertsMedicineCardiffJulia Roberts
15PoliticsLondonKateMossPoliticsLondonKate Moss
Mentor Allocation (2)

Last edited: