Google Sheets Lesson 7 – INDEX & MATCH

In our last example we reorganized our data set by student email instead of by feedback comment. Now we want to pull the data from the Sorted sheet over into the Unique sheet, so that we have separate columns for Score 1, Eval 1, Score 2, Eval 2, etc.

Note that we could do the first Score and Eval through a VLOOKUP, but we can’t do Score 2 or Eval 2 that way because that data is on the row below the VLOOKUP match.

In order to fix this, we need to use a more complex, and more powerful, alternative to VLOOKUP called INDEX & MATCH.

Click here to open up Example 7 and look at the highlighted cell in the Unique tab.

INDEX & MATCH work together to get the job done. MATCH will return the relative row of a column of data that matches a specific value. MATCH takes 3 values:

MATCH(Search Key, Range, 0)

The 0 refers to a searchtype; normally we use 0 to indicate that it needs to be an exact match. Thus if our data set is

The formula =MATCH(“student03@unishanoi.org”,A:A,0) will return a 4.

Now it’s time for the INDEX function. This will return the exact cell we need in a range (not just the correct column). It take three values as well:

• INDEX(Range, Row offset, Column offset)

For Range, we will enter in our whole search range, in this case

• Range = Sorted!B:D.

Next we enter in our MATCH formula, which will act as the Row offset:

• Row Offset = MATCH(A2,Sorted!B:B,0)

We put this formula in the B2 cell, and it looks up the student email in A2, then returns the row that it finds that email in the B column of the Sorted sheet.

Finally, we have our Column offset, or the number of rows we want to count over from our range. Unlike VLOOKUP, where the initial column is counted as 1, here we start counting at 0. Thus, our the score value would be found with a column offset of 2.

We are left with this formula in cell B2:

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

We can copy and paste this formula down for the whole B column, but we will need to adjust it for Columns C, D, and E.

For Score 2 and Eval 2, we will need to change the Row Offset from the MATCH value to the MATCH value + 1. For example:

• MATCH(A2,Sorted!B:B,0)+1

For Eval 1 and Eval 2, we will need to change the Column Offset to 3 instead of 2.

This take a little practice, so take your time with it. Try to use the B2 formula as a basis to fill in the rest of the values.

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