Last Updated on October 31, 2023 by Jake Sheridan
In this tutorial, you will learn how to use Index and Match functions in Google Sheets.
How To Use Index Match in Google Sheets
The INDEX and MATCH functions are both examples of powerful and versatile functions in Google Sheets. The pair of functions are often used together in order to perform more complex lookups.
The INDEX function lets you reference cells from a range, based on their position in the range.
The MATCH function allows you to find the position of a value in a range.
By combining these two functions, you can perform powerful lookups that would otherwise be difficult to do. Unlike the VLOOKUP, we can perform lookups with INDEX and MATCH without having the lookup row being in the left-most column.
In this guide, we will explain how to use the INDEX and MATCH functions together in Google Sheets.
How To Set Up an Index-Match Formula in Google Sheets
Here’s how to create a Google Sheets formula that uses the INDEX and MATCH formulas together.
First, the user must identify the lookup table range, the lookup value, and the column to output.
In this example, we’ll use the product ID values in column A as our lookup values. We’ll use the range F2:I23 as our lookup table. Lastly, we want to return the corresponding sales count for each matched product ID.
Select the cell you want to output the lookup result to. Type “=MATCH(“ to start the MATCH function.
The first argument of the MATCH function should be the lookup value. In this example, we want to match the string “P_00008722”.
Next, we’ll identify the lookup range. In this case, we want to find the lookup string in the product ID column. The range must be a one-dimensional array else the formula will return an error.
The third argument specifies the search type to use. A value of ‘1’ indicates that the range is sorted in ascending order and we want to return the largest value less than or equal to the lookup value.
A value of ‘-1’ is the reverse, where we assume that the range is sorted in descending order and we want to return the smallest value greater than or equal to the lookup value.
A value of 0 indicates that we want to return an exact match. This is the type of search you need to use when your lookup range is not sorted.
Next, we’ll wrap our MATCH function with an outer INDEX function. The MATCH function will be placed as the INDEX function’s second argument.
The first argument of the INDEX function refers to the lookup table range. The third argument will control which column in the lookup table to return.
The output of the MATCH function essentially becomes the row number of the value we’ll be returning.
We can convert the lookup table ranges to absolute references.
This will allow users to automatically fill the rest of the column.
This guide should be everything you need to use INDEX and MATCH to lookup values in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.