I made a guide on how to use INDEX MATCH in Google Sheets. Below, I’ll show you how to break this into its two component functions, explain their syntax, and explore a few practical examples.
When used alone, the Google Sheets INDEX and MATCH functions have limited applications. However, when combined, they can be potent. When used together, they can provide an excellent and, to some, even a superior alternative to the VLOOKUP function.
In this INDEX MATCH Google Sheets guide, we’ll look into what each function does and how they work together. We’ll also cover why this master combo can be superior to VLOOKUP. Read on to learn everything you need to know about INDEX MATCH in Google Sheets.
Table of Contents
Why Use INDEX and MATCH in Google Sheets
To simplify it, LOOKUP functions in Google Sheets are ideal for finding what you need when you have a large amount of data. Meanwhile, the INDEX and MATCH functions can look up data automatically rather than requiring a person to do so manually.
INDEX MATCH Google Sheets Overview
To get a better idea of what the MATCH and INDEX functions are and how they work (their syntax and definition), let’s dive into the details. To start with, I made a video that explains the whole process.
The MATCH function in Google Sheets returns the relative position or “INDEX” of an item within a range of cells. It takes a cell range and a value and returns the position of that value within the cell range. On the other hand, the INDEX function in Google Sheets retrieves the value itself based on its row and column offsets.
INDEX Function Syntax
To understand any function in Google Sheets, first you need to learn the syntax. So let’s break down the syntax of the INDEX function first. It shows how the formula pulls data from your sheet, and it shows what you’ll need to input to get the result you expect.
Here’s the syntax for the INDEX Formula:
=INDEX(reference, [row], [column])
- reference: This refers to the range to look in. It is a required element.
- row: This represents the number of rows to offset from the very first cell of your range. Optional: 0 if omitted.
- column: Similar to a row, it refers to the number of offset columns. Also optional: 0 if omitted.
With the INDEX function, we can find the value in the cell in a specific position. Let’s look at our example sheet below:
For this example, we would use the formula:
=INDEX(A2:E6,2,2)
This formula returns the value in the cell that is in the second row and second column of the specified range.
MATCH Function Syntax
So I already broke down the syntax for the first function. What about the next? The MATCH function in Excel is a powerful function that allows you to search for a specific value within a range of cells and returns the relative position of that value within the range.
Here’s the syntax for the Google Spreadsheet MATCH Function:
=MATCH(search_key, range, search_type)
- Search_key: This is the item that we want to MATCH. This can be text, numeric value, cell reference, or formula.
- Range. This refers to the range of cells in which we want to search for an item MATCHing the search_key.
- Search_type: This is an optional parameter. It specifies the kind of MATCHing we want. It can be one of the following values:
- 0 – This value specifies that the search should be done for an exact MATCHing item. This option is usually used when our range is not assumed to be sorted in any order.
- 1: This is the default value. This option assumes that the range is already sorted in ascending order. Specifying this parameter as 1 returns the largest value less than or equal to the search_key.
- -1: This option assumes that the range is already sorted in descending order. Specifying this parameter as -1 returns the smallest value greater than or equal to the search_key.
Unlike the INDEX function, MATCH returns the position of the value it looks up in relation to the range. Let’s look at our example:
The formula returns the value 4, and according to the range we selected, the word Samsung is in the 4th cell.
Now that we have gone through their syntaxes, we know that these functions are useful on their own, but when you are using both of them together, you will get a great alternative for other add-ons, such as VLOOKUP.
Combining INDEX and MATCH
These INDEX MATCH sheet formulas work better when combined. We need to know the syntax of both of them combined.
To begin, when combining INDEX and MATCH in Google Sheets, you will begin with the INDEX function’s formula. The MATCH portion of the formula fills the position argument.
When the two functions are combined, the syntax looks like this:
INDEX(return array, MATCH(lookup value, lookup array))
When combined, the INDEX MATCH formula Google Sheets can look up a value in a cell from a table and return the corresponding value in another cell in the same row or column.
If you’re still a little confused, don’t worry. We’re about to take a look at a practical example.
Step-By-Step Guide on How to Use INDEX MATCH in Google Sheets
As promised, I’m including step-by-step instructions on how to use INDEX MATCH in Google Sheets. This guide uses photos of my own design, and it provides a practical example of a product inventory.
Prefer to learn how to use INDEX MATCH with multiple criteria? I’ve got a more detailed video on that, too.
Follow along as I show you how to use my inventory spreadsheet and the INDEX MATCH function in Google Sheets to look for the number of items by company name.
Step 1: Go to an empty cell and use the syntax:
=INDEX(cell_range,MATCH(“value”,cell_range,0))
In the screenshot above, I have used the following formula:
=INDEX(B3:B6,MATCH("Apple",A3:A6,0))
To look up the values in the cell range B3:B6 and MATCH with the company name “Apple” in cell range A3:A6.
Add a Drop-down List to Make the Search Easier
Step 1: Go to an empty cell, right-click, and go to “View more cell actions,” then click on “Data Validation.” From there, select cell range A3:A6 and click on “Save.”
Step 2: Next, go to the cell with the drop-down and add the formula:
=INDEX(B3:B6,MATCH(B9,A3:A6,0))
In the screenshot above, I have used cell B9 as the MATCH key since that is where I have created the dropdown. Every time I select a different company from the drop-down, Google Sheets will show the value relevant to that company. This way, I won’t have to change the formula when searching for a different company.
That’s how simple it is to use INDEX MATCH in Google Sheets. You can use this function on your Google Sheets to make your searches easier, while adding a drop-down will definitely come in handy!
Case-Sensitive V-lookup with INDEX MATCH in Google Sheets
In Google Sheets, the VLOOKUP function itself is not case-sensitive. However, you can achieve a case-sensitive VLOOKUP using the INDEX and MATCH functions together with the EXACT function. The EXACT function compares two strings and returns TRUE if they are the same (including case), or FALSE otherwise.
In our example, we can look up the number of phones for Samsung products in lowercase:
Here’s how you can do a case-sensitive VLOOKUP with INDEX MATCH in Google Sheets in our example sheet. Type in the following formula:
=INDEX(B2:B9, MATCH(TRUE, EXACT(A13, A2:A9), 0))
Using FIND with INDEX MATCH
Another way to perform a case-sensitive VLOOKUP in Google Sheets is by adding the FIND function to the INDEX MATCH formula.
The formula used in this example is:
=ArrayFormula(INDEX(B3:B9, MATCH(1, FIND(A13, A3:A9)), 0))
The FIND function searches the range for the value with the same case as the search key. In this case, the value is “Samsung.” The INDEX function then returns the value that corresponds to it in the species range, which in our case, is in column B. The 0 returns an exact match.
Other Methods for Searching Different Sheets
There are many ways to search data in other sheets. Each does something a little different. Here are the three most common ways to search different sheets in Excel and Google Sheets are using:
- INDEX and MATCH
- VLOOKUP
- XLOOKUP
INDEX and MATCH, VLOOKUP, and XLOOKUP are all used to look up data and return results. Each function requires a unique syntax for the formula. But which one should you use and when?
VLOOKUP Function
We have gone over what INDEX and MATCH are, but we have yet to really get an in-depth explanation of how the VLOOKUP function works. For a long time, the VLOOKUP function has been a popular reference function in Google Sheets and Excel.
Because the V stands for Vertical, you’re doing a vertical lookup from left to right with VLOOKUP. So why would anyone use INDEX and MATCH instead of VLOOKUP? Because VLOOKUP only works when your lookup value is to the left of the desired return value. However, this can also have a negative impact on users who want a more flexible tool and effortless work experience.
The syntax for VLOOKUP is as follows:
VLOOKUP(lookup_value, lookup_array, column_number, range_lookup)
The last argument is optional as TRUE (approximate match) or FALSE (exact match). If we did the opposite and looked up a value in the fourth column and returned the matching value in the second column, we would not get the desired result and might even get an error.
According to Microsoft, for VLOOKUP to work properly, the LOOKUP value should always be in the first column of the range. For example, if your LOOKUP value is in cell C2 — your range should begin with C.
On the other hand, to compare the two given functions, the INDEX and MATCH function covers the entire cell range or array, making it a more robust lookup option even if the formula is more complicated and harder to understand.
XLOOKUP Function
Now lastly, there is the XLOOKUP function.
XLOOKUP is an Excel reference function that came after VLOOKUP and its counterpart HLOOKUP. It wasn’t available in Sheets until recently. XLOOKUP differs from VLOOKUP in that it works regardless of where the lookup and return values are in your cell range or array.
The Syntax for XLOOKUP Is:
XLOOKUP(lookup_value, lookup_array, return_array, not_found, match_mode, search_mode)
The first three arguments are required and are similar to that in the VLOOKUP function. XLOOKUP offers three optional arguments at the end for giving a text result if the value isn’t found, a mode for the type of match, and a mode for how to perform the search.
With this in mind, you can see that XLOOKUP is a better option than VLOOKUP simply because you can arrange your data any way you like and still receive your desired result.
Although there are a few other add-ons you will be able to find on Google, these have proven to be the most efficient ones. One may seem to be better than the other, but all work great in their own way.
In addition to the above-mentioned methods, you can also utilize HLOOKUP and other LOOKUP formulas. To learn more about these formulas, check out our other articles, where we go into detail about them.
Frequently Asked Questions
Is VLOOKUP Better Than INDEX MATCH?
INDEX MATCH is often considered to be the better option. It is never slower than VLOOKUP. It returns a reference rather than a value, allowing us to use it for a variety of purposes. It also makes no difference where the result array is in relation to the lookup array.
However, some people find the INDEX MATCH formula for Google Sheets harder to understand and get the hang of, even though it is much faster and far more flexible.
Can You MATCH 2 INDEX?
MATCH INDEX Google Sheets formulas are typically configured with the MATCH function set to look through a one-column range and provide a match based on specified criteria. There is no way to supply more than one criterion without concatenating values in a helper column or in the formula itself.
This formula circumvents this limitation by using boolean logic to generate an array of ones and zeros to represent rows that meet all three criteria, then using MATCH to MATCH the first one found.
Is There Something Better Than INDEX MATCH?
The strongest opponent for the Google Sheets MATCH INDEX formula is XLOOKUP. XLOOKUP can perform similar searches but is much easier to understand.
Conclusion
To sum up what we learned in this INDEX MATCH Google Sheets guide, there are plenty of search functions in Google Sheets. INDEX MATCH has proven to be one of the most effective ones. But it may be worth learning more about how to use XLOOKUP, VLOOKUP, and HLOOKUP too.
Related:
- How To VLOOKUP From Another Sheet
- How To VLOOKUP Multiple Criteria in Google Sheets
- How To Get the Last Value From a Column in Google Sheets
- A Guide to the IFNA Google Sheets Function
- How To Calculate Age in Excel [12 Easy Examples]
- 4 Easy Examples of Greater Than or Equal To in Excel
