INDEX MATCH in Google Sheets – flexible Vlookup for your spreadsheets

When you need to find data in your sheet that corresponds to a certain key record, it is usually Google Sheets VLOOKUP you turn to. But there you go: VLOOKUP slaps you with limitations almost immediately. That's why you'd better increase the resources for the task by learning INDEX MATCH.

INDEX MATCH in Google Sheets is a combination of two functions: INDEX and MATCH. When used in tandem, they act as a better alternative for Google Sheets VLOOKUP. Let's find out their capabilities together in this blog post. But first, I'd like to give you a quick tour of their own roles in spreadsheets.

Google Sheets MATCH function

I'd like to start with Google Sheets MATCH because it's really simple. It scans your data for a specific value and returns its position:

=MATCH(search_key, range, [search_type])
  • search_key is that record you're looking for. Required.
  • range is either a row or a column to look in. Required.

    Note. MATCH only accepts one-dimensional arrays: either row or column.

  • search_type is optional and defines if the match should be exact or approximate. If omitted, it is 1 by default:
    • 1 means the range is sorted in ascending order. The function gets the largest value less than or equal to your search_key.
    • 0 will make the function look for the exact match in case your range is not sorted.
    • -1 hints that records are ranked using descending sorting. In this case, the function gets the smallest value greater than or equal to your search_key.

Here's an example: to get a position of a certain berry in the list of all berries, I need the following MATCH formula in my Google Sheets:

=MATCH("Blueberry", B1:B10, 0) Lookup the exact match using Google Sheets MATCH function.

Google Sheets INDEX function

While MATCH shows where to look for your value (its location in the range), Google Sheets INDEX function fetches the value itself based on its row and column offsets:

=INDEX(reference, [row], [column])
  • reference is the range to look in. Required.
  • row is the number of rows to offset from the very first cell of your range. Optional, 0 if omitted.
  • column, just like row, is the number of offset columns. Also optional, also 0 if omitted.

If you specify both optional arguments (row and column), Google Sheets INDEX will return a record from a destination cell:

=INDEX(A1:C10, 7, 1) Use INDEX for Google Sheets to fetch a record from a particular cell.

Skip one of those arguments and the function will get you the entire row or column accordingly:

=INDEX(A1:C10, 7) Omit the column argument to fetch the entire row.

How to use INDEX MATCH in Google Sheets — formula examples

When INDEX and MATCH are used together in spreadsheets, they are at their mightiest. They can absolutely substitute Google Sheets VLOOKUP and fetch the required record from a table based on your key value.

Build your first INDEX MATCH formula for Google Sheets

Suppose you'd like to get the stock info on cranberry from the same table I used above. I only swapped columns B and C (you'll find out why a bit later).

  1. Now all berries are listed in column C. Google Sheets MATCH function will help you locate the exact row of the cranberry: 8

    =MATCH("Cranberry", C1:C10, 0)

  2. Put that whole MATCH formula to a row argument in the INDEX function:

    =INDEX(A1:C10, MATCH("Cranberry", C1:C10, 0))

    This one will return the entire row with cranberry in it.

  3. But since all you need is the stock info, specify the number of the lookup column as well: 3

    =INDEX(A1:C10, MATCH("Cranberry", C1:C10,0), 2)

  4. Voila! Use Google Sheets INDEX MATCH to look up values.
  5. You can go further and give up that last column indicator (2). You won't need it at all if you use only the lookup column (B1:B10) rather than the entire table (A1:C10) as the first argument:

    =INDEX(B1:B10, MATCH("Cranberry", C1:C10, 0)) Use a column instead of the whole table as the first argument.

Tip. A more convenient way to check the availability of various berries would be to place them in a drop-down list (E2) and refer your MATCH function to the cell with that list:

=INDEX(B1:B10, MATCH(E2, C1:C10, 0))

Once you select the berry, the related value will change accordingly: Refer to the cell with the drop-down list.

Why INDEX MATCH in Google Sheets is better than VLOOKUP

You already know that Google Sheets INDEX MATCH looks your value up in a table and returns another related record from the same row. And you know that Google Sheets VLOOKUP does exactly the same. So why bother?

The thing is, INDEX MATCH has some major advantages over VLOOKUP:

  1. Left-side lookup is possible. I changed the columns places earlier to illustrate this one: INDEX MATCH function in Google Sheets can and does look to the left of the search column. VLOOKUP always searches the very first column of the range and looks for matches to its right — else, it gets only #N/A errors: Look up to the left with Google Sheets INDEX MATCH.
  2. No messed up references when adding new columns and moving existing ones. If you add or move columns, INDEX MATCH will reflect the changes automatically without meddling in the result. Since you use column references, they are instantly adjusted by Google Sheets: See how formulas adjust without messing with the result.

    Go ahead and try to do this with VLOOKUP: it requires the order number rather than cell references for a lookup column. Thus, you'll just end up getting the wrong value because another column takes the same place — column 2 in my example: Vlookup pulls wrong data.

  3. Considers text case when necessary (more on this right below).
  4. Can be used for vertical lookup based on multiple criteria.

I invite you to look at the last two points in detail below.

Case-sensitive v-lookup with INDEX MATCH in Google Sheets

INDEX MATCH is a go-to when it comes to case-sensitivity.

Supposing all berries are being sold in two ways — loose (weighed at the counter) and packed in boxes. Hence, there are two occurrences of each berry written in different cases in the list, each with its own ID that also vary in cases: Same names, same IDs, different text cases.

So how can you look up the stock info on a berry sold in a certain way? VLOOKUP will return the first name it finds no matter its case.

Luckily, INDEX MATCH for Google Sheets can do it correctly. You'll just need to use one additional function — FIND or EXACT.

Example 1. FIND for case-sensitive Vlookup

FIND is a case-sensitive function in Google Sheets which makes it great for case-sensitive vertical lookup:

=ArrayFormula(INDEX(B2:B19, MATCH(1, FIND(E2, C2:C19)), 0)) Use the FIND function to build case-sensitive INDEX MATCH.

Let's see what happens in this formula:

  1. FIND scans column C (C2:C19) for the record from E2 (cherry) considering its letter case. Once located, the formula "marks" that cell with a number — 1.
  2. MATCH searches for this mark — 1 — in the same column (C) and hands the number of its row to INDEX.
  3. INDEX comes down to that row in column B (B2:B19) and fetches the required record to you.
  4. When you finish building the formula, press Ctrl+Shift+Enter to add ArrayFormula at the beginning. It is required because without it FIND won't be able to search in arrays (in more than one cell). Or you can type 'ArrayFormula' from your keyboard.

Example 2. EXACT for case-sensitive Vlookup

If you replace FIND with EXACT, the latter will look for records with the exact same characters, including their text case.

The only difference is that EXACT "marks" a match with TRUE rather than number 1. Hence, the first argument for MATCH should be TRUE:

=ArrayFormula(INDEX(B2:B19, MATCH(TRUE, EXACT(E2, C2:C19), 0))) Use the EXACT function to build case-sensitive INDEX MATCH.

Google Sheets INDEX MATCH with multiple criteria

What if there are several conditions based on which you'd like to fetch the record?

Let's check the price of the cherry that is being sold in PP buckets and is already running out: Find out the price on several criteria.

I arranged all the criteria in the drop-down lists in column F. And it is Google Sheets INDEX MATCH that supports multiple criteria, not VLOOKUP. Here's the formula you will need to use:

=ArrayFormula(INDEX(B2:B24, MATCH(CONCATENATE(F2:F4), A2:A24&C2:C24&D2:D24, 0),)) How to use INDEX MATCH in Google Sheets with multiple criteria.

Don't panic! :) Its logic is actually quite simple:

  1. CONCATENATE(F2:F4) combines all three records from cells with criteria into one string like this:

    CherryPP bucketRunning out

    This is a search_key for MATCH, or, in other words, what you're looking for in the table.

  2. A2:A24&C2:C24&D2:D24 constitute a range for the MATCH function to look in. Since all three criteria take place in three separate columns, this way you kind of combine them:

    CherryCardboard trayIn stock
    CherryFilm packagingOut of stock
    CherryPP bucketRunning out
    etc.

  3. The last argument in MATCH — 0 — makes it possible to find the exact match for CherryPP bucketRunning out among all those rows of combined columns. As you can see, it's in the 3rd row.
  4. And then INDEX does its thing: it fetches the record from the 3rd row of column B.
  5. ArrayFormula is used to allow other functions to work with arrays.

Tip. If your formula doesn't find a match, it will return an error. To avoid that, you can wrap this entire formula in IFERROR (make it the first argument) and enter whatever you want to see in a cell instead of errors as a second argument:

=IFERROR(ArrayFormula(INDEX(B2:B27, MATCH(CONCATENATE(F2:F4), A2:A27&C2:C27&D2:D27, 0),)), "Not found") Use IFERROR to overwrite potential errors.

Better alternative to INDEX MATCH in Google Sheets — Filter and Extract Data

Whatever lookup function you prefer, VLOOKUP or INDEX MATCH, there's a better alternative to them both.

Filter and Extract Data is a special add-on for Google Sheets designed to:

  • lookup without formulas
  • lookup in all directions
  • search by multiple conditions for different data types: text, numbers, dates, time, etc.
  • fetch several matches, as many as you need (providing there are as many of them in your table, of course)


Google Workspace Marketplace badge

The interface is straightforward, so you won't have to doubt whether you're doing everything correctly:

  1. Select source range.
  2. Set the number of matches and columns to return.
  3. Fine-tune the conditions using the predefined operators (contains, =, not empty, between, etc.).
Tweak the conditions in Filter and Extract Data.

You will also be able to:

  • preview the result
  • decide where to place it
  • and how: as a formula or just values
Tweak the conditions in Filter and Extract Data.

Don't miss out on the opportunity to try the add-on. Go ahead and install it from Google Workspace Marketplace.

Google Workspace Marketplace badge

Its tutorial will explain every option in detail.

We also prepared a special instructional video:

Vlookup multiple matches from multiple sheets & update the related data – Merge Sheets add-on

The next level would be to not just pull the matches but to look them up in multiple sheets at once and update the related values in the neighboring columns of your main table.

The quickest way to do that is using the Merge Sheets add-on for Google Sheets.

Tip. You will find more ways to match & merge your Google Sheets data in this article.

There are 5 steps where you:

  1. Select your main sheet (the one where you'd like to pull the data to).
  2. Select all your lookup sheets (those to match with the main sheet and pull the data from): Select several lookup sheets.
  3. Identify matching columns.
  4. Specify the columns to update (in the main sheet) or even add (from the lookup sheet(s)): Select columns to update or add to the main sheet.
  5. Tweak additional options such as highlight changes, update only blank cells, etc.

Watch the demo video with the add-on in action below or look through its tutorial page.

Though the video doesn't feature adding multiple sheets, the latest update brings this option to your spreadsheets. Install Merge Sheets from Google Marketplace and prove me right ;)

See you in the comments below or in the next article ;)

Table of contents

79 comments

  1. Doe not work especially when you want to use it witch real world stock data

    =INDEX(A25:AA510, MATCH(M3, A25:A510,1), 26)

  2. I would like to utilise the Import Match formulas to populate from another spreadsheet altogether. I was looking at using the importrange function with this but everytime I try I return varying errors.

    Any advice on how this can be overcome would be greatly appreciated.

    Thanks,

    Liam

    • Hello Liam,

      Please provide the formula you're trying to use and specify what error exactly (upon hovering a cell with the formula) it returns.

  3. I am trying to make it so that my formula can pull multiple responses from my main sheet. I have two columns, one to list the items and the second to mark if I am "out" of said item. After a few attempts at messing around with the function I've only been able to get it to pull the name of the last item on the first list that is "out."

    Is there a way to write it so that this formula will return all of the items on my list that are marked as such?

    • Hi! I’m sorry, but your description doesn’t give me a complete understanding of your task. Correct me if I’m wrong, to get a list of values in the column according to the criterion, write this formula in cell D3:

      =ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX($A$3:$A$13, SMALL(IF(D$2=$B$3:$B$13, ROW($A$3:$A$13)-2,""), ROW()-2)),"")), 1, 1)

      A - column of values
      B - criteria column
      D2 -cell with criterion

      • This did work, but I had to copy the formula down the column to get multiple responses. Thankfully it didn't repeat data so I can use this but I was really wanting it to auto-populate the entire list without needing to copy the formula multiple times.

        Sorry my original post wasn't as clear an explanation as what I had in my head, I essentially have two lists on my first sheet that look like this:

        Column A | Column B | Column C | Column D ... and so on

        Product 1 | Out | Product 5 | Out
        Product 2 | Out | Product 6 | Low
        Product 3 | Low | Product 7 | Out
        Product 4 | Good | Product 8 | Good

        My goal is for the second sheet to only show me the items that are marked at as "Out" in Column A, C, and so forth. I prefer the data from each set stays separate so on my end retyping the formula once for each is fine.

          • Thank you, that works perfectly! I definitely need to stop overcomplicating things in my head when the solution ends up being so simple :)

  4. Good day,

    I am trying to add a column into a Google Sheet. I will use an example.
    I have 2 sheets lets name them Sheet A and Sheet B. Both of these sheets have different information shared between each other and also tabs using all kind of Vlookup(s) etc. etc.

    Both these sheets are used to load and retrieve data from.

    In Sheet A I need to add a Colum. However when I add the column in this Sheet A, the Tab needs to retrieve information from Sheet B. It retrieves some data but it messes up the information in the cells which seems that it moved calculations or the obvious reason is that the column where the information are retrieved moved. But this tell me that the formulas (cross referencing to different tabs) did not automatically update when I added the Colum in Sheet A. I expected that the Google Sheet will as like a Excel spreadsheet automatically update the formulas in a column or cell when it was moved.

    As I thought the formulas in both sheets will update and as this is not happening, I am lost on how to fix the sheet to allow the adding of the column without "breaking" the lookup and other formulas.
    The Sheet B uses "importrange" to retrieve information from Sheet A but the information in one of the tabs inside Sheet B is then screwed up when I add the Column in Sheet A.

    If I remove the column from Sheet A all data restores back. How can I add the Column in Sheet A without messing up the information in Sheet B's one tab?

    Thank you for any advise.
    Kind regards
    Werner

    • Good day, Werner.

      Ranges adjust themselves automatically if they are written as pure ranges if I may say so. For example, in VLOOKUP the 2nd arguments requires such a range:
      =VLOOKUP($A$2,Sheet2!B1:C30,2)
      It will adjust itself accordingly whenever you add other columns/rows to Sheet2.

      But in IMPORTRANGE you have to write the range as a text string inside double quotes:
      =IMPORTRANGE(spreadsheet_url,"Sheet1!A1:D100")
      Such records won't adjust themselves automatically since they are text strings. You will have to edit them manually after interfering with their source data.

      • Hi

        I want to make the v lookup from another sheet which are having dates in rows and need to have excet date in base sheet

        • Hi Ashish,

          Sorry, I don't understand what the last part of your task mean: "need to have excet date in base sheet". Have you tried INDEX MATCH from this article?

  5. Natalia, thanks for a great explanation. Crystal clear and I like your humor.

  6. XLOOKUP should be mentioned, or even featured, in this article. It solves many of the VLOOKUP limitations, such as searching to the left.

  7. how can i use Index and match to select particular information from different file ?

  8. Hi Natalia,
    This solution is brilliant, thanks so much for sharing.

    It almost works for what I need, The word I'm looking for is contained as part of a phrase in the rows for the range.
    MATCH has to find exactly the word exactly and not as part of the string, right?

    Is there a way to modify this to find the searched word inside a phrase?

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)