Google Sheets – INDEX and MATCH (VLOOKUP alt.)

In this post, we’re going to look at how to use INDEX and MATCH to look up data and see how it can be more powerful than the commonly used VLOOKUP.

In a previous post, we looked at how we can quickly look up tables for certain information, using the VLOOKUP function. This function is great but it does have some limitations.

Firstly, when you look up information in the table you always have to look to the right for the matching information.

Secondly, if you add a new column to the table, this messes up the references and it returns the wrong information.

This is where the functions INDEX and MATCH come in. With a combination of these two, you can look either left or right in a table, and it adapts to any added columns.

---

INDEX

First, let’s see how the INDEX function works. Here, we have 2 columns of data (columns A and B), we can pick out a certain cell’s data by referring to its position in the table of data.

Formula to copy & paste: =INDEX(A1:B3,3,2)

---

INDEX and MATCH

Example 1 – Finding the classroom a specific teacher is in

Usually we don’t already know the exact row and column we want, and this is where MATCH comes in.

Here, we have a table which continues a list of teachers and which classroom they are in. I want to be able to type in cell D1 and find the classroom the teacher is in, and return it in cell D2.

MATCH

First, we need to understand the following MATCH function.

=MATCH(D1, A2:A9, 0)

This looks for the teacher’s name in D1 within the list of names (A2:A9). The 0 at the end looks for an exact match. This returns the number 3, as that is the number of places down the list in which Barney is. Now we have the row, we need to use that to find the classroom.

INDEX and MATCH together

We do this by adding the INDEX function. This looks down the classroom list (range B2:B9), and picks the value at the place returned by the MATCH function. This is 3 as we saw above. This is classroom A3.

So, to reiterate that, the first range (B2:B9) is the column you want to return the value from. The second range (A2:A9) is the column you want to initially search in.

=INDEX(B2:B9, MATCH(D1, A2:A9,0))

In this example, we could have used the following VLOOKUP function, which as you can see returns the same result in cell E2.

=VLOOKUP(D1,A2:B9,2,FALSE)

---

Example 2 – Finding who is in a particular classroom (looking up to the LEFT in a table)

In the above example, we looked up a teacher and then moved RIGHT, to look up the classroom. But what happens if we want to do the opposite and look up the classroom and move LEFT and find out which teacher is in that room?

In cell E2, we have the following VLOOKUP formula and as we can see above, it returns a N/A error. This is because VLOOKUP cannot look to the left, as the column number is always positive, i.e. moves to the right.

=VLOOKUP(D1,A2:B9,1,FALSE)

To be able to do it, in cell D2 we can use the following INDEX and MATCH formula:

=INDEX(A2:A9,MATCH(D1,B2:B9,0))

Here, all we do is swap the formula we saw earlier around. We return the teacher in range A2:A9 by matching the classroom in range B2:B9.

---

Example 3 – The effect of inserting a column in a table on an INDEX/MATCH and a VLOOKUP formula

Another problem with using the VLOOKUP formula is that if a column is added and deleted from the table being used, it returns the wrong result, as the column reference is then incorrect.

Below we have started with the same table and formulas that we had in example 1. Then I have added a new column with the class year in those classrooms. As we can see, the INDEX/MATCH formula in cell E2, correctly returns that the Bob is in classroom B2. The VLOOKUP formula in cell F2, returns the class year and not the classroom.

As we can see, the VLOOKUP formula, still refers to the second column even though the range now covers 3 columns and we in fact now want the third column.

=VLOOKUP(E1,A2:C9,2,FALSE)

Whereas, the INDEX/MATCH formula, adapts and looks up the classroom now in column C.

=INDEX(C2:C9,MATCH(E1,A2:A9,0))

---

Example 4 – Returning more than one column of information

We’re not limited to just returning a cell in one specific column, we can return multiple columns or an entire row if we want. Here, I want to return both the class year (in E2) and classroom (in F2), when the teacher is entered in cell D2.

We use the following formula:

=INDEX(B2:C9,MATCH(D2,A2:A9,0))

Here, the output range is B2:C9, which includes both the year and classroom. Note, that this returns the information, in separate columns to the right of the cell with the formula in it, i.e. cells E2 and F2.

---

Example 5 – Matching a range and not an exact figure

So far, we’ve been looking for exact matches, but like VLOOKUP we can also match within ranges.

Here, we have a level test where the score corresponds with the student’s level. If they get between 12 and 15 they are intermediate, and so on. In cell D1, we’ll add the score, then in D2 we’ll see the level.

We use the following formula:

=INDEX(A2:A9,MATCH(D1,B2:B9,1))

This is the same as we’ve seen before, except that there is a “1” at the end this time. This time it’s looking for 14 in column B and looks for the largest value that is the same or less than it. As 14 doesn’t appear, it finds that 12 is the largest value. Then it returns the corresponding level in column A. So, basically, you can imagine a range from 12-14 for Intermediate, and that our value falls within that range.

There are 3 number options at the end of the formula:

1: causes MATCH to assume that the range is sorted in ascending order and return the largest value, less than or equal to search key.

0: indicates exact match, and is required in situations where range is not sorted.

-1: causes MATCH to assume that the range is sorted in descending order and return the smallest value greater than or equal to search key.

So, if the list was sorted in descending order we would use a “-1” instead. By default, “1” is used, so it is possible to leave the number out if you want to search a list in ascending order, like we did in this example.

Try out Index and Match

In many cases VLOOKUP will do the job, but as you can see above there are times when INDEX and MATCH is better. It’s less used partly because it looks more complicated but as we’ve seen above it’s easy to set up.

Have you checked out the new XLOOKUP function? It’s way better than VLOOKUP! Check out my post on it here.

---