combine 2 lists with group assignment

Normally, I don't respond to posts that do not contain a sample workbook. I am not in the minority on this. In the future, you'll find you'll get a faster response if you include a sample workbook.

However, this request requires very little setup, so I was willing to do it.

Since you have Office 365, you have tables. One of the benefits of a table is that it knows how big it is, so as you add or delete rows, formulas, pivot tables and charts automatically change to reflect the new range of the table.

In this case I have two tables: Table_Mentors and Table_Mentees. Fill them in as needed.

The second column of the mentee table (column D on the spreadsheet) has a list-style data validation on it: =INDIRECT("Table_Mentors[Mentors]") - so the list is dynamic. It will keep pace as you add or delete names from the Mentor Table.

This second table is used to make assignments. I am assuming that a mentor can have more than one mentee but not the other way around.

Reporting is then done using pivot tables. Two are included on this sheet: one shows the mentors; the other shows the mentees and who is mentoring them. In both cases, you can see that Dewey and Maggie don't have mentors yet.

And as an extra bonus, if you call now - a third pivot table absolutely free! (Ginsu steak knives not included). This was an after thought, I decided to show a third pivot table that shows mentees that don't have a mentor. The same filter can be used to see who is mentoring who.

How to add an attachment:

It may be simply a matter of right clicking on the pivot table and selecting refresh.

Also you might do better assigning the mentors on the mentee table and build the pivot from it. If you need help, shout. I have a project going so I may not be as responsive as I'd like to be.