Here is another variant (based on fjns solution). I used defined Tables here, so all formulas will adjust automatically when new records are added, and used Data Validation to avoid any typing errors when new data are entered. And I renamed sheets on more logical way.

And my variant allows the same test to be run several times! The only case when the group isn't returned on Scores sheet, is when the group or test is not defined. Data Validation prevents this when data are entered manually, but this validation can be bypassed when user copy-pastes Data into Tables.

Why using a single sheet do define tests is preferable? Because with your design, whenever you add a new group in future, you have to redesign the whole workbook (or all workbooks, when you have to go with several ones). With all tests defined in single Table, all you need is to add a new croup into Groups Table.

Attached Files

TestScores.xlsx (13.6 KB, 8 views)