Open up Example 8 and make a copy for yourself. We will start with Row 2 of Unique, in cell F2. We want to see if there is a third entry for the student listed in cell A2. To find this third entry, we need to use another INDEX MATCH:
In this case our index range is only a single column: Sorted!B:B. From that column, we find the first listing that matches A2, get the relative row number, then add 2 and return the result. We can this with an IF formula in cell F2:
For rows 2-4 and 9-11, this should result in “no”, meaning that there is no Score 3. For rows 5-8 it should come up as “yes”, meaning that there is a Score 3. Now we take the formula for Score 2 in cell D2:
and we add 2 instead of 1 to tell Google to take the value 2 down from the first email match. We get:
We replace the “yes” with the INDEX formula just above, and replace the “no” with “” to make it blank. When we copy this formula down the F column, we should get only the scores for the group of 4.
Try to use this same process for the Eval 3 column, taking care to change the Column Offset value so that it pulls in the comment rather than the score.
Google Sheets Lessons 1-9
Click on a lesson below for a written tutorial demonstrating the lesson.
- Lesson 1 – CONCATENATE
- Lesson 2 – VLOOKUP
- Lesson 3 – ARRAYFORMULA
- Lesson 4 – formatting ARRAYFORMULA
- Lesson 5 – QUERY
- Lesson 6 – UNIQUE
- Lesson 7 – INDEX & MATCH
- Lesson 8 – conditional INDEX & MATCH
- Lesson 9 – Autocrat Add-on