Google Sheets search functions make life easier. Among those functions are the INDEX and MATCH, when used alone, the Google Sheets INDEX and MATCH functions appear to have limited applications. However, when combined, they can be potent. In fact, when used together, they can provide an excellent and, to some, even a superior alternative to the VLOOKUP function.
In this article, we will go over Google Sheets INDEX MATCH, what each function does separately, and how they work together. We’ll also go over why we believe this master combo is superior to VLOOKUP. Read on to learn everything you need to know about index match in Google Sheets.
Why Use INDEX and MATCH in Google Sheets
To make it simpler, these Lookup functions in Google Sheets are ideal for finding what you need when you have a large amount of data. And INDEX and MATCH can look up data automatically rather than requiring a person to do so manually. The time a person can save depends on how long it takes them to look up the data manually, how many records are being looked up, and if they understand how to use the formulas correctly.
Overview of INDEX MATCH in Google Sheets
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.
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.
The Syntax:
Let’s get into the syntax of both of these functions.
The Syntax for the INDEX Formula:
=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.
The Syntax for the Google Spreadsheet MATCH Function:
=MATCH(search_key, range, search_type)
• search_key – is the item that we want to MATCH. This can be text, numeric value, cell reference, or formula.
• range – is the range of cells in which we want to search for an item MATCHing the search_key.
• search_type – 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 exactly 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
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 sheets 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 are combined, the syntax looks like this:
INDEX(return array, MATCH(lookup value, lookup array))
When combined, the INDEX MATCH formula Google Sheets offers 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
For this guide, we will use the following Inventory spreadsheet and the INDEX MATCH function Google Sheets offers 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
=INDEX(B3:B6,MATCH("Apple",A3:A6,0))
To look up the values in cell range B3:B6 and MATCH with the company name “Apple” in cell range A3:A6.
Add a Dropdown 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 to the cell with the dropdown, 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, and I everytime I select a different company from the dropdown, 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.
And done! That’s how simple it is to use INDEX MATCH in Google Sheets. You can use this function on your Google Sheet to make searching very easy, and adding a dropdown will definitely come in handy!
Other Methods
So far we have learned that LOOKUP functions in Microsoft Excel and Google Sheets are ideal for finding what you need when you have a large amount of data. But we also noticed that there are various ways you can do this, the three most common ways to do this are INDEX and MATCH, VLOOKUP, and XLOOKUP. But you may be wondering what’s the difference between them.
INDEX and MATCH, VLOOKUP, and XLOOKUP are all used to look up data and return a result. They each function slightly differently and require a unique syntax for the formula. Which should you use when? Which is superior? Let’s take a look so you can decide which option is best for you.
VLOOKUP function
We have gone over what INDEX and MATCH are, but we haven’t really gotten 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, which can be a downside to many users as they desire a more flexible tool to get an easier work experience.
The syntax for VLOOKUP is:
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 matchinging 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. If your lookup value is in cell C2, for example, 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 a bit more complicated and hard 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. 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, all work great in their own way.
In addition to the above-mentioned methods, you can also utilize HLOOKUP and other LOOKUP formulas. Check out our other articles, where we go into detail about these formulas.
Frequently Asked Questions
Is VLOOKUP better than INDEX MATCH?
If you’re wondering if the VLOOKUP function is superior to INDEX MATCH? The answer is no, Google Sheet INDEX MATCH is far superior: It is never slower than VLOOKUP and can be significantly faster. It returns a reference rather than a value, allowing us to use it for a variety of purposes. It makes no difference where the result array is in relation to the lookup array. However, to each their own. Some people find the INDEX MATCH formula Google Sheets harder to understand and get a 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. You may be wondering if you can MATCH 2 INDEX, There is no way to supply more than one criteria 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?
There are various add-ons you can use, and each has features that outweigh the others, though the INDEX MATCH seems to be the best option yet, the strongest opponent for the Google Sheets MATCH INDEX formula seems to be the XLOOKUP formula in Excel.
So far it looks like XLOOKUP is very similar to INDEX MATCH. The major difference is that we can perform the same lookup in one function (XLOOKUP) instead of two (INDEX MATCH function Google Sheets). However, Microsoft gave us some additional features with XLOOKUP that make it even more useful. However, in the end, all these add-ons work great for those who know how to work well with them.
Conclusion
To sum up what we learned in this article, there are various add-ons in Google Sheets and Excel that help us look up data and save us a lot of time. INDEX MATCH in Google Sheets has proven to be one of the most effective ones, but we have seen that the other alternatives work just as well at times.
This may make you wonder “Should I use XLOOKUP or INDEX and MATCH?” Here are some things to consider. You must have a good idea of the function you’re going to use.
And we may have noticed that, despite it being very useful, the function can be complicated to understand.
If you’ve used the INDEX and MATCH Google Sheets functions separately and together to look up values, you’ll be more familiar with how they work. If it’s not broken, don’t fix it, and continue to use what makes you happy. Of course, if your data is set up to work with VLOOKUP and you’ve been using it for years, you can keep using it or make the simple switch to XLOOKUP in Excel, leaving INDEX and MATCH in the dust.
If you want to create a simple, easy-to-build formula in any direction, XLOOKUP can replace INDEX and MATCH. You don’t have to worry about combining two functions’ arguments into one or rearranging your data.
Whatever method you choose, make sure you know your way around it. Google Sheets and Excel formulas have once again proven to be very useful in situations where you need to save time and be more organized.
Related:
