Google Sheets Lesson 8 – conditional INDEX & MATCH

In the last example, we used the functions INDEX and MATCH to reorient our data so that each student was listed on a single row with their peers assessments in columns to the right. If all of the groups were of equal size, we would be done manipulating the data. However, two of the groups had 3 students and one had 4. How do we pull in data for Score 3 and Eval 3 columns for only one of the groups?To do this we need to use what we will call a 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

Examples / Challenges