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:
Note. MATCH only accepts one-dimensional arrays: either row or column.
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:
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:
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).
=MATCH("Cranberry", C1:C10, 0)
=INDEX(A1:C10, MATCH("Cranberry", C1:C10, 0))
This one will return the entire row with cranberry in it.
=INDEX(A1:C10, MATCH("Cranberry", C1:C10,0), 2)
=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:
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:
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:
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:
CherryPP bucketRunning out
This is a search_key for MATCH, or, in other words, what you're looking for in the table.
CherryCardboard trayIn stock
CherryFilm packagingOut of stock
CherryPP bucketRunning out
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:
The interface is straightforward, so you won't have to doubt whether you're doing everything correctly:
You will also be able to:
We also prepared a special instructional video:
See you in the comments below or in the next article ;)
Table of contents