First Perfect Hit - The Match Function

A couple of months ago I shared with you how to create Boss Battles using Google Forms and Sheets and assign XP automatically using the Vlookup function. I spent most of my summer creating all the Boss Battles I intended to use this school year and tweaking my individual student sheets so that if a student scored a "perfect" hit on the boss they would capture it and add it to their collection.

The response of my students to this whole idea has been very encouraging. They actually look forward to Boss Battle days, literally rushing to class to participate in what you and I would actually call a testing day. However, there were a couple of little nagging issues that my more hardcore gamers immediately asked for: Extra XP for "first hit" and/or for "first perfect hit". Apparently, this is a "thing", and while they were satisfied with my answer at the time "Don't know how to do that other than scouring the sheet, which I do not want to do. If you figure it out, we can implement it", it stayed in the back of my head. 

A couple of days ago, I once again sat down to try to figure it out, and it finally crystallized.

On one of my existing Boss Battle Sheets I added:

- First hit: Easy, since you only need to bring up the first entry on the form

='Form Responses 1'!$B$2

- First perfect hit: This formula looks up the e-mail of the student Form Responses sheet (column B) and returns the value found in column C when it equals 10 (the perfect score for this Boss battle). Once it finds the first one, it stops.

=index('Form Responses 1'!B:B, match(true, 'Form Responses 1'!C:C=10, 0))  

These two formulas made the students happy since at least they now had the "bragging rights". The addition of extra XP for those two instances can be done manually, or by adding a "Perfect" sheet to your BossBattle (as in the example), where you again report those two values and input whatever XP you wish for them. You can then use the Vlookup function explained in my previous post to have Google sheets find the value for you. That formula in your leaderboard would look something like:

=IFERROR(VLOOKUP(A2,IMPORTRANGE("GoogleSheetID","Perfect!A:B"),2,0), 0)

I now have to add those formulas to all my Boss Battles, but I know that it will be a nice surprise when we come back from break. What about you, any other tweaks to share?