by Natalia Sharashova, updated on

*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.

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", A1:A10, 0)`

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)`

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

`=INDEX(A1:C10, 7)`

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.

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).

- 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)`

- 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.

- 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)`

- Voila!

- 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))`

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:

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:

**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:

**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:

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:

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

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

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:

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.

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))`

Let's see what happens in this formula:

- 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*. - MATCH searches for this mark —
*1*— in the same column (*C*) and hands the number of its row to INDEX. - INDEX comes down to that row in column B (
*B2:B19*) and fetches the required record to you. - 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.

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)))`

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*:

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),))`

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

*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.*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.- 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. - And then INDEX does its thing: it fetches the record from the 3rd row of column B.
- 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")`

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

Multiple VLOOKUP Matches 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)

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

- Select source range.
- Set the number of matches and columns to return.
- Fine-tune the conditions using the predefined operators (
*contains, =, not empty*,*between*, etc.).

You will also be able to:

- preview the result
- decide where to place it
- and how: as a formula or just values

Don't miss out on this opportunity to check the add-on. Go ahead and install it from Google Workspace Marketplace. Its tutorial page will explain every option in detail.

We also prepared a special instructional video:

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

Table of contents