How to create automated pairing of teams based on historic data

Dear experts,

Urgent need requested!

I am struggling with creating a list that automatically pairs team members based on historic team data.

Question 1:
E.g. if Jean teamed up with Edward in 2014, with Louise in 2015 and with Tom in 2016, would it be possible for Excel to suggest a team member in 2017 other than Edward, Louise or Tom?

Question 2:
Furthermore, would Excel be able to also suggest the same piece of fruit (as an example) to every team in 2017 which is again different from the fruit each team member had in the previous years (this is more tricky given that both team members of each new team in 2017 had different pieces of fruit the years before which reduces the number of possibilities).

One extra difficulty is that there are 7 members in the team. So every year, one person will be a team on itself. How to deal with this in Excel?

I've attached some dummy data to explain in more detail.
- Columns B-E contain the historic data (team setup and fruit).
- In columns G-M I already could identify what type of fruit each tember got the past 3 years and with whom they've teamed up during the same period.
- tabs "Fruit" and "Team Members" contain the input for the validation lists

If I now enter the 7 team members in Column C for the year 2017 (row 24-30), I would like Excel to suggest a partner and piece of fruit in columns D and E (marked in yellow).

(It speaks for itself that 2018 would need to follow the same logic but then with 2017 data as extra input.)

I really hope one of you can help me solve this!!

Best regards,

Thomas