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)
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)
Skip one of those arguments and the function will get you the entire row or column accordingly:
=INDEX(A1:C10, 7)
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).
- 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:
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:
- 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.
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:
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))
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.
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)))
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:
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")
Better alternative to INDEX MATCH in Google Sheets — Multiple VLOOKUP Matches
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:
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:
- Select your main sheet (the one where you'd like to pull the data to).
- Select all your lookup sheets (those to match with the main sheet and pull the data from):
- Identify matching columns.
- Specify the columns to update (in the main sheet) or even add (from the lookup sheet(s)):
- 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 ;)
You may also be interested in
- Trap and fix errors in your Google Sheets VLOOKUP
- Google Sheets QUERY function: a spreadsheet cure-all you have yet to discover
- Google Sheets FILTER function: formulas and tools to filter data in spreadsheets
FAQs
INDEX MATCH in Google Sheets – another way for vertical lookup? ›
Better alternative to INDEX MATCH in Google Sheets — Multiple VLOOKUP Matches. 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.
Can you index match vertically? ›INDEX/MATCH can work with vertical and horizontal ranges
After all, the V in VLOOKUP stands for vertical. VLOOKUP can only go through data that is vertical, while INDEX/MATCH can go through data vertically as well horizontally.
Ultimate Guide to XLOOKUP in Google Sheets (Updated August 2022) XLOOKUP is a function in Google Sheets and Microsoft Excel that can identify values in an array or range quickly. The function is more versatile than LOOKUP, VLOOKUP, and HLOOKUP.
Can you index match vertical and horizontal? ›Combining the INDEX & MATCH functions allows you to perform lookups by matching values using multiple criteria. In other words, it allows you to disambiguate in cases where there are multiple potential matches for a given value. These lookups can be vertical, horizontal, or both.
Does match function work vertically? ›Since the MATCH function works efficiently with vertical and horizontal arrays, you can perform the function twice to perform a two-way lookup. This allows you to generate a row position and a column position respectively.
Why use INDEX match instead of XLOOKUP? ›XLOOKUP and INDEX and MATCH are both flexible and powerful lookup solutions in Excel. For difficult lookup problems that require backward compatibility with older versions of Excel, INDEX + MATCH is the clear winner, since XLOOKUP is only available in Excel 2021 and later.
What is vertical lookup in Google Sheets? ›The VLOOKUP function in Google Sheets is designed to perform a vertical lookup - search for a key value (unique identifier) down the first column in a specified range and return a value in the same row from another column.
What is the difference between Xlookup and lookup? ›Conclusion. To recap, the key differences between an XLOOKUP and a VLOOKUP are: XLOOKUP can look for values to the left and right of the lookup array, while VLOOKUP is limited to only looking for values to the right of the lookup value column.
Can you use INDEX match for Hlookup? ›HLOOKUP supports approximate and exact matching. The INDEX function in Excel is fantastically flexible and powerful, and you'll find it in a huge number of Excel formulas, especially advanced formulas.
Can you INDEX match row and column? ›The INDEX-MATCH-MATCH combines two MATCH statements into the row and column positions in an INDEX formula. In doing so, you're making a formula that's fillable left, right, up or down for when you merge or find data.
Can you use INDEX match for columns? ›
The INDEX MATCH function is one of Excel's most powerful features. The older brother of the much-used VLOOKUP , INDEX MATCH allows you to look up values in a table based off of other rows and columns. And, unlike VLOOKUP , it can be used on rows, columns, or both at the same time.
Can you use Xlookup instead of INDEX-match? ›How to use the XLOOKUP function in Excel. XLOOKUP was released by Microsoft 365 in 2019 and is meant as the replacement for VLOOKUP, HLOOKUP, INDEX/MATCH functions.
Is VLOOKUP ever better than INDEX-match? ›VLOOKUP is better because it is easier to understand for beginner to intermediate Excel users. INDEX-MATCH is better because it will continue to work if you insert or delete columns in the lookup table and allows the lookup column to be anywhere in the table.
What is the difference between VLOOKUP INDEX-match and Xlookup? ›VLOOKUP defaults to the closest match whereas XLOOKUP defaults to an exact match. To correct that in VLOOKUP, you have to type FALSE as your fourth argument.
What is the difference between match and xmatch function? ›XMATCH defaults to an exact match, while MATCH defaults to an approximate match. XMATCH can find the next larger item or the next smaller item. XMATCH can perform a reverse search (i.e. search from last to first). XMATCH does not require values to be sorted when performing an approximate match.
Does INDEX match work in Google Sheets? ›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.
Does VLOOKUP only work on vertically arranged data? ›If you're using VLOOKUP, your data set needs to be arranged vertically in a table, with each column representing different data. Once you've added your lookup value to your new VLOOKUP formula, you'll need to identify the cell range containing your data. To do this, add the range to your open VLOOKUP formula.
Which function is better than INDEX match? ›Excel's VLOOKUP function is more popular than the INDEX-MATCH function combination, probably because when Excel users need to look up data then a “lookup” function would be a reasonable choice to make.
What is the difference between INDEX match and Xmatch? ›In summary, the XMATCH function is same as MATCH but more flexible and robust. It can look up both in vertical and horizontal arrays, search first-to-last or last-to-first, find exact, approximate and partial matches, and use a faster binary search algorithm.
What is the best use of INDEX match? ›INDEX MATCH is made up of two functions. The MATCH function is used to figure out where the lookup value is located in a range of cells. The INDEX function then returns the value or cell reference from within a table or range. The MATCH and INDEX functions are combined to do the job that VLOOKUP cannot do.
How to do a vertical lookup? ›
- In the Formula Bar, type =VLOOKUP().
- In the parentheses, enter your lookup value, followed by a comma. ...
- Enter your table array or lookup table, the range of data you want to search, and a comma: (H2,B3:F25,
- Enter column index number. ...
- Enter the range lookup value, either TRUE or FALSE.
Select the data that you want to convert or transpose. Copy the data by clicking right and select copy or use the keyboard shortcut Control + C. Select the cell where you want to drop the transposed data. Right-click and select Paste Special, click on Paste Transpose.
What is the difference between VLOOKUP and Hlookup in Google Sheets? ›- The VLOOKUP vs HLOOKUP helps users retrieve the required data using the lookup_value.
- VLOOKUP or Vertical LOOKUP is used to retrieve column-wise data.
- HLOOKUP or Horizontal LOOKUP is used to retrieve row-wise data.
- Both functions have the same arguments.
It first looks for Gross Profit in column B, then looks for Qtr1 in the top row of the table (range C5:F5), and finally returns the value at the intersection of the two. This is similar to using the INDEX and MATCH functions together. Tip: You can also use XLOOKUP to replace the HLOOKUP function.
What are the different types of lookup? ›There are two forms of LOOKUP in Excel: Vector and Array.
How fast is VLOOKUP vs INDEX match? ›In our table, the VLOOKUP formula calculated in 2.3 seconds, INDEX MATCH in 2.6 seconds, OFFSET and MATCH in 2.7 seconds, and XLOOKUP in 3.3 seconds. As you see, the calculation speed increases significantly compared to ranges. Interestingly enough, Excel tables are very fast even with regular references.
How does Hlookup work in Google Sheets? ›Horizontal lookup. Searches across the first row of a range for a key and returns the value of a specified cell in the column found.
How do I match data in Google Sheets? ›How to use the MATCH formula in Google Sheets. Type “=MATCH” or go to “Insert” → “Function” → “Lookup” → “MATCH”. Select a range in which you will find a match with the “search_key”. Define how to search if necessary.
Can VLOOKUP & INDEX match do the same function? ›VLOOKUP is a single formula that does all the lookup-and-fetch, but with INDEX/MATCH, you need to use both the functions in the formula. INDEX/MATCH can more advanced lookup – such as lookup to the left of the dataset, make row/column value dynamic, etc.
Can I use array formula with INDEX match? ›INDEX MATCH with multiple criteria is an 'array formula' created from the INDEX and MATCH functions. The synergies between the INDEX and MATCH functions are that: MATCH searches for a value and returns a location. MATCH feeds the location to the INDEX function.
What is the alternative for VLOOKUP and Hlookup? ›
One of the most useful functions available in Excel is the LOOKUP function. This allows you to take any value entered, find it in a data range, then return a value or information from that same data range without having to scroll through a list.
How do you INDEX match multiple results horizontally? ›Re: INDEX-MATCH Multiple Results Horizontally
You can combine data from multiple cells into a single cell using the Ampersand symbol (&) or the CONCAT function. To combine rows with different functions, you can use CONCATENATE function. To combine data from multiple rows into one row, you can use TRANSPOSE function.
Combining the INDEX and MATCH formulas allows you to look up the value within a cell in a table based on both horizontal and vertical criteria. The INDEX and MATCH combination is a quicker and more flexible alternative to formulas with similar functions, such as VLOOKUP and HLOOKUP.
Which columns are not good for indexing? ›Columns with the following characteristics are less suitable for indexing: There are many nulls in the column and you do not search on the non-null values.
Can you use match across multiple columns? ›We can use an array formula that is based on the MMULT, TRANSPOSE, COLUMN, and INDEX functions to lookup a value by matching across multiple columns.
What are the disadvantages of INDEX match? ›The main disadvantage of INDEX and MATCH is that it's not well known. Therefore other people working on your workbook might not immediately understand it. Applying the INDEX and MATCH combination is comparatively difficult.
Is INDEX match faster than VLOOKUP Google Sheets? ›Higher processing speed.
But if your worksheets contain hundreds or thousands of rows, and consequently hundreds or thousands of formulas, MATCH INDEX will work much faster than VLOOKUP because Excel will have to process only the lookup and return columns rather than the entire table array.
Although Microsoft Excel provides special functions for vertical and horizontal lookup, expert users normally replace them with INDEX MATCH, which is superior to VLOOKUP and HLOOKUP in many ways. Among other things, it can look up two or more criteria in columns and rows.
Does INDEX match have to be in ascending order? ›If the data isn't in ascending order, MATCH can return incorrect results or incorrect #N/A values.
Can you do INDEX match by row? ›While the VLOOKUP function can only look for a value in the first column of data to return an adjacent value, using the INDEX and MATCH functions together allows you to search any column and return a value in any row.
Does INDEX match work with columns? ›
Unlike VLOOKUP, INDEX-MATCH can index multiple columns for fillable output. In other words, the array can be multiple columns.
Can indexing be done on multiple columns? ›5, “Column Indexes”). MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on.
How do I return multiple columns with index match? ›- We will click on Cell H3.
- We will insert the formula below into Cell H3. =INDEX(Section,MATCH(1,MMULT(--(Names=G3),TRANSPOSE(COLUMN(Names)^0)),0))
- Because this is an array formula, we will press CTRL+SHIFT+ENTER.
One of the popular improvements to the VLOOKUP limitations is to combine 2 Excel functions, INDEX and MATCH. Also, the INDEX/MATCH combination runs faster than VLOOKUP, which can make a difference on large sets of data.
How do you structure an INDEX match? ›- Type “=MATCH(” and link to the cell containing “Kevin”… the name we want to look up.
- Select all the cells in the Name column (including the “Name” header).
- Type zero “0” for an exact match.
- The result is that Kevin is in row “4.”
Match Index is a value created by BP and it means 'give me the Nth element matching my chosen attributes'. Match Reverse is similar and means 'search the application in reverse order'. Ordinal is a value created by the target application, that means 'this is the nth child element within the parent element'.
What is the difference between match and INDEX match? ›INDEX and MATCH Introduction
The INDEX function can return an item from a specific position in a list. The MATCH function can return the position of a value in a list. The INDEX / MATCH functions can be used together, as a flexible and powerful tool for extracting data from a table.
- Step 1: Insert a normal INDEX MATCH formula.
- Step 2: Change the MATCH lookup value to 1.
- Step 3: Write the criteria.
Apart from retrieving a single cell, the INDEX function is able to return an array of values from the entire row or column.
Can you index match with two column criteria? ›Index Match can be used if you have multiple criteria that you need to check in order to get the resultant value. Let's understand this in a detailed step-by-step tutorial below.