*conditional*INDEX MATCH. This means that before we use INDEX MATCH, we check to see if there are 2 or 3 assessment listings in the Sorted tab for each given student.

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:

INDEX(Sorted!B:B,MATCH(A2,Sorted!B:B,0)+2)

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:

=IF(A2=INDEX(Sorted!B:B,MATCH(A2,Sorted!B:B,0)+2),”yes”,”no”)

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:

INDEX(Sorted!B:D,INDEX(MATCH(A2,Sorted!B:B,0)+1),2)

and we add 2 instead of 1 to tell Google to take the value 2 down from the first email match. We get:

INDEX(Sorted!B:D,INDEX(MATCH(A2,Sorted!B:B,0)+2),2)

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