The INDEX & MATCH Functions

• Introduction
• Method

How to link data from one part of a Google Sheet to another (or even between different sheets) using the INDEX and MATCH functions.

Introduction

The ability to lookup data from another part of a spreadsheet is tremendously important to building really useful solutions using sheets. It gives us the power to maintain a list that we can effectively choose from, then other cells can be populated with further details based upon the choice we have made. Not only does this save time (reducing the amount of typing we have to do) but it also ensures consistency and accuracy by reducing / highlighting erroneous data.

Method

When constructing lookups, most people would normally turn to the two dedicated functions, vlookup and hlookup. However, there are a number of reasons why you might want to use index and match instead.

index / match is more powerful than vlookup, frequently faster for larger data sets and it isn’t really harder to use at all. Using this combination gets round the most common issue with normal lookups, namely you cannot lookup (return) values that are left of the value you are matching. This commonly means that your lookup table has to be arranged so that the key values are in the first few columns, or you end up having to put extra columns in to replicate data that originally appears on the wrong side of your key column.

Using the table of data above, to find a particular value using the match function we might use the following formula. This will return the row number of the first row where the value ‘Person’ occurs in the C (third) column. The result will be four (counting the header column as one). If we wanted to exclude the header column from the match, we could specify the range as C2:C.

MATCH("Person", C:C, 0)

Now, with a traditional lookup, we would only be able to return values from the D (fourth) column. So if we wanted to return the given name of the first individual with a family name of ‘Person’, we’d be a bit stuffed.

Enter Index

INDEX(B:B, MATCH("Person", C:C, 0), 1)

By passing our returned value (4) to the index function as the row number, we can precisely specify the cell value we would like to retrieve. In this case, we want the value at column 1, row 4 in the range B:B. If we had opted to exclude the header rows then we would also need to do the same in our index function, otherwise we’d would return the value for the cell above (-1) the one we wanted. We could also widen our indexing range to A2:D and return the second column (this would do exactly the same thing).

INDEX(A2:D, MATCH("Person", C2:C, 0), 2)

Using index gives extra flexibility, because you can use a further match condition to return the column number. This is especially useful when you need to pull a value from a different sheet that matches a row header and column header (e.g. student id & assignment title) in your sheet.