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.

First, we state the table range (A1:B3), then state the row number we want (3), then the column number (2). This returns the value in the cell (300).

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.

This post is taken from my book “Google Sheets Functions – A step-by-step Guide“, available on Amazon here.

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


Want to learn more about Google Workspace and Apps Script? The books below are available on Amazon. Just click on a book! (Affiliate links).

JavaScript Fundamentals for Apps Script users

a

Google Apps Script Projects 1
Google Apps Script Projects 2

19 comments

  1. Really very helpful, but I have one question. In exemple 1, If we have more than one teacher for the same classroom, how could I solve this? For exemple, Barney and Roseane at A3. I would like to see something like this:

    A3:
    Barney
    Roseane

    Thank you

  2. This was helpful! Question for a project I’m working on:

    What if you have multiple rows with “Fred” in column A and you want to return the highest value that appears next to Fred in column B (classroom)?

    What I’m actually doing is:
    1) taking two columns with numeric values
    2) identifying the max value in column A
    3) identifying the max value in column B from among the rows that have column A’s max value in column A

    1. Hi-I would use a QUERY function here. Try =QUERY(A2:B7, “select max(A), max(B)”) Changing the range to what you data set is. That will display the max value in A and the corresponding maximum value for that from B.

  3. Lol, no, I didn’t. It selects the maximum value from each column, but not the max in A from only the rows with the max value in B. Sorry, would delete my comment above if I could.

    1. Here we go:

      B1 is blank
      B2 = max(B3:B100)
      A1 = query(A3:B100,”select max(A) where B >= “&B2&””)
      A2 contains the output from A1

      Sorry to be annoying, but wanted to resolve this.

      1. Maybe you’re looking for something like this =QUERY(A2:B6, “select A, max(B) where A=’Fred’ group by A”)

        In column A there are names including Fred and values in column B. This gets the maximum value for Fred.

  4. Great tutorial! Still struggling with following issue though:

    I have a list with:
    – Column A: contains values (numbers)
    – Column B: references (in this case unique values (aka countries))

    Now the idea is that I have made a ranking of the figures in Column A (1-10) for reporting reasons using LARGE and I want do display the reference next to that ranking. I need that reference for additional reporting…

    Here is the problem: If I rank a top 10 I will get eg. 3 times the same value, a VLOOKUP or INDEX/MATCH however will always return the same value as a reference.

    Question: How do I get a formula to move on to the next value so the references are different?

    1. Hi Sven,
      Would you be able to share the file with me so I can see exactly what you’re trying to do? Or at least a file with the an example set of data.
      Send it to my email baz@bazroberts.com and I’ll take a look.

  5. Hi, I have an issue regarding filter views.

    Normally, an INDEX MATCH works just fine when sorting data from the source that I am trying to index function, however, my company is using google sheets extensively, and therefore require us to use filter “views” versus regular filters and for some reason, when a filter view is applied and the data is sorted in the filter view, the index match that was referencing the data will break…but ONLY for the user that is using the filter view. All other sheet users see no change.

    Let me simplify with an example:

    Sheet1 contains The Data that Sheet2 is referencing with INDEX MATCH: =INDEX(Sheet1!B:B, MATCH(A1, Sheet1:A:A, 0))

    In Sheet1, someone applies a filter view and sorts it however they want it sorted, but as they do this, their view of Sheet2 with the Index formula has returned a completely different value than it should due to the filter view.

    Does that make sense? Remove the filter view and the function is fixed or if the data is sorted normally and NOT in a filter view, the formula works just fine.

    Is there a solution to this?

    1. It seems that INDEX MATCH is still looking at the original position of what you’re searching for.
      One way around it is to use something like QUERY on a separate sheet to read the data from the original sheet and place it on that new sheet.
      The QUERY function then looks at the new table which will show the filtered version, but correctly find the related value you want. Here’s an example of the QUERY formula:
      =QUERY(Sheet1!A1:B4,”select A, B”)

  6. hello, thanks for the article. I have a weird issue when I concat the value from the index/match expression the value turns into a decimal number. Any ideas?

    =INDEX(DailySchedule!B5:DailySchedule!B26, match(D1, DailySchedule!D5:DailySchedule!D26, 0))
    The above expression result give me my expect value of 9:30AM

    =INDEX(DailySchedule!B5:DailySchedule!B26, match(D1, DailySchedule!D5:DailySchedule!D26, 0))& ” Friday”
    Above expression gives me this 0.395833333333333 Friday

    Maybe a date to string issue?

    1. =”Friday ” & text(INDEX(DailySchedule!B5:DailySchedule!B26, match(D1, DailySchedule!D5:DailySchedule!D26, 0)), “hh:mm am/pm”)

      Friday 09:30 AM

      Yes date to string was the issue. Sorry to waste your time.
      Thanks

      Mark

Comments are closed.