Creating a Coach-Student Pairing Algorithm in Google Sheets

Other Questions Google Sheets
1

I’m working with survey data from two groups - coaches and students. Coaches submit their info including expertise areas, job level, and time zone. Students fill out what they want to learn and their preferred schedule.

I need to create a pairing system with these rules:

  1. Each coach can only work with a maximum of 2 students.
  2. The compatibility should be represented as a percentage.

I want to implement this solution in Google Sheets. Here’s the formula I tried, but it’s not functioning correctly:

=ArrayFormula(
IFERROR(
  IF(
    ROW($C:$C)=1,
    "Compatibility %",
    IF(
      COUNTIF($D$2:$D$5,$C2)<=2,
      MAX(
        FILTER(
          $D$2:$D$5,
          COUNTIF($K$1:K1,$D$2:$D$5)<2
        )=$D$2:$D$5
      )*
      SUMPRODUCT(
        D2:G2,
        {0.5, 0.25, 0.15, 0.1},
        $I2:$J2,
        {0.7, 0.3}
      )/
      (
        SQRT(SUMSQ(D2:G2))*
        SQRT(SUMSQ($I2:$J2))
      ),
      ""
    )
  )
)
)

Can anyone suggest how to fix this matching formula? I’m having trouble getting the percentage calculation to work correctly while ensuring that each coach has no more than 2 students.

2

that formula’s doin way too much at once. split it up - check coach capacity separately first with =COUNTIFS(assignments_range,coach_name)<=2 in its own column. then only run your percentage calculations where that’s TRUE. the SUMPRODUCT looks fine, but all that array filtering is probably breakin things. start simple - match one student at a time instead of cramming everything into one formula.

3

Your formula’s way too complicated for what you need. I’ve built similar matching systems in Sheets - breaking this into separate steps works way better than cramming everything into one monster formula. First, set up a compatibility matrix with coaches as columns and students as rows. Use a simpler percentage calc based on your weighted criteria: =SUMPRODUCT((coach_criteria=student_criteria)*weights)/SUM(weights)*100. Handle the constraint separately. Add a helper column tracking how many students each coach has. Use COUNTIF to monitor this. For assignments, use RANK or LARGE functions to find the highest compatibility scores while checking your constraint condition. The real problem is you’re mixing percentage calculation with constraint logic. Sheets hates this kind of nested conditional stuff in array formulas. If you need real-time optimization, go with Apps Script. Otherwise, the step-by-step approach should handle most cases just fine.

4

I’ve hit this exact problem building a mentor-student matching system. Your formula’s trying to do two things at once - calculate compatibility AND enforce constraints - which breaks the array logic.

Here’s what worked for me: split it into two phases. First, calculate all compatibility scores in a clean matrix without constraints. Use something like =SUMPRODUCT(coach_skills*student_needs*weight_array)/SUMPRODUCT(weight_array)*100 for each coach-student pair.

Then create your assignment logic separately. Reference those compatibility scores but apply the two-student limit using LARGE with INDEX/MATCH to grab the highest compatibility pairs. Use COUNTIF to check if coaches are available.

The trick is realizing optimization problems need iterative logic, not single formulas. Google Sheets handles this way better when you separate scoring from constraint checking.