Allocating Mentors to Students based on criteria

[Georgieee_xo]

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)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]First Name[/TD]
[TD]Surname[/TD]
[TD]Area of interest[/TD]
[TD]Location[/TD]
[TD]Mentor[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Bloggs[/TD]
[TD]Finance[/TD]
[TD]London[/TD]
[TD]*VLOOKUP result*
Tom Jones[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD]Law[/TD]
[TD]Birmingham[/TD]
[TD]*VLOOKUP result*
Amy Adams[/TD]
[/TR]
[TR]
[TD]Ryan[/TD]
[TD]Davies[/TD]
[TD]Politics[/TD]
[TD]London[/TD]
[TD]*VLOOKUP result*
John Smith[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Doe[/TD]
[TD]Medicine[/TD]
[TD]Cardiff[/TD]
[TD]*VLOOKUP result*
Julia Roberts[/TD]
[/TR]
[TR]
[TD]Emily[/TD]
[TD]Smith[/TD]
[TD]Politics[/TD]
[TD]London[/TD]
[TD]*VLOOKUP result*
John Smith**
[/TD]
[/TR]
</tbody>[/TABLE]

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

Mentor Data (example)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Helper
[/TD]
[TD]First Name
[/TD]
[TD]Surname
[/TD]
[TD]Practice Area
[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]PoliticsLondon[/TD]
[TD]John[/TD]
[TD]Smith[/TD]
[TD]Politics[/TD]
[TD]London[/TD]
[/TR]
[TR]
[TD]FinanceLondon[/TD]
[TD]Tom[/TD]
[TD]Jones[/TD]
[TD]Finance[/TD]
[TD]London[/TD]
[/TR]
[TR]
[TD]LawBirmingham[/TD]
[TD]Amy[/TD]
[TD]Adams[/TD]
[TD]Law[/TD]
[TD]Birmingham[/TD]
[/TR]
[TR]
[TD]MedicineCardiff[/TD]
[TD]Julia[/TD]
[TD]Roberts[/TD]
[TD]Medicine[/TD]
[TD]Cardiff[/TD]
[/TR]
[TR]
[TD]PoliticsLondon[/TD]
[TD]Kate[/TD]
[TD]Moss[/TD]
[TD]Politics[/TD]
[TD]London[/TD]
[/TR]
</tbody>[/TABLE]

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

Thank you

 

[Peter_SSs]

Welcome to the MrExcel board!

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

Excel Workbook
	A	B	C	D	E	F
1	First Name	Surname	Area of interest	Location	Mentor
2	Joe	Bloggs	Finance	London	Tom Jones
3	Jane	Doe	Law	Birmingham	Amy Adams
4	Ryan	Davies	Politics	London	John Smith
5	John	Doe	Medicine	Cardiff	Julia Roberts
6	Emily	Smith	Politics	London	Kate Moss
7	Ann	Smith	Politics	London	None Available
8
9
10	Helper	First Name	Surname	Practice Area	Location	Full Name
11	PoliticsLondon	John	Smith	Politics	London	John Smith
12	FinanceLondon	Tom	Jones	Finance	London	Tom Jones
13	LawBirmingham	Amy	Adams	Law	Birmingham	Amy Adams
14	MedicineCardiff	Julia	Roberts	Medicine	Cardiff	Julia Roberts
15	PoliticsLondon	Kate	Moss	Politics	London	Kate Moss
Mentor Allocation

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

Excel Workbook
	A	B	C	D	E	F
1	First Name	Surname	Area of interest	Location	Mentor	Area/Location
2	Joe	Bloggs	Finance	London	Tom Jones	FinanceLondon
3	Jane	Doe	Law	Birmingham	Amy Adams	LawBirmingham
4	Ryan	Davies	Politics	London	John Smith	PoliticsLondon
5	John	Doe	Medicine	Cardiff	Julia Roberts	MedicineCardiff
6	Emily	Smith	Politics	London	Kate Moss	PoliticsLondon
7	Ann	Smith	Politics	London	None Available	PoliticsLondon
8
9
10	Helper	First Name	Surname	Practice Area	Location	Full Name
11	PoliticsLondon	John	Smith	Politics	London	John Smith
12	FinanceLondon	Tom	Jones	Finance	London	Tom Jones
13	LawBirmingham	Amy	Adams	Law	Birmingham	Amy Adams
14	MedicineCardiff	Julia	Roberts	Medicine	Cardiff	Julia Roberts
15	PoliticsLondon	Kate	Moss	Politics	London	Kate Moss
Mentor Allocation (2)