When you have a large amount of data in your spreadsheet, looking up specific data can take time. VLOOKUP is a handy function but has its limits. Instead, you can use INDEX and MATCH in Google Sheets.
While VLOOKUP is a function that works well, it’s only useful when the value you look up is to the left of what you want to return. For instance, if you want to return a value in column C, then the lookup value must be in column A or B.
With INDEX and MATCH, you can have your lookup and return values anywhere in your sheet. This makes the duo a more useful option in many situations.
The INDEX Function
Before you create the formula for INDEX and MATCH combined, it’s good to know the syntax and arguments for each function separately.
The syntax for the INDEX function is INDEX(reference, row, column) where only the first argument is required. Let’s look at an example.
Here, we want to return the value in row 2, column 1 in the cell range A1 through F13. We would use this formula:
=INDEX(A1:F13,2,1)
The formula gives us the result January.
The MATCH Function
Now let’s try out the MATCH function which returns the position of a lookup value in a one-dimensional range.
The syntax for the MATCH function is MATCH(lookup, reference, search_type) where the first two arguments are required. The search_type argument uses 1 as the default and assumes the reference is sorted in ascending order. You can use 0 to find an exact match or -1 if the reference is sorted in descending order.
Here, we want to return the location of September in our range A1 through A13, so we’d use this formula with an exact match search_type:
=MATCH("September",A1:A13,0)
You can see we receive the result 10 because September is in row 10.
Note: If you look up text, be sure to include it in quotation marks.
Using INDEX and MATCH Together
When you combine INDEX and MATCH in Google Sheets, you’ll start with the INDEX function’s formula. The MATCH portion of the formula fills the argument for position (row, column).
Here, we want to look up the sales for pop sockets in January. We would use this formula:
=INDEX(F1:F13,MATCH("January",A1:A13,0))
To break this down, MATCH looks up January in the range A1 through A13 as an exact match. It returns that position to INDEX which looks up the matching result in the range F1 through F13 and returns the value $888.
Let’s look at one more example going the opposite direction of our sheet. Here, we want to look up the month that has pop socket sales of $777.00. We would use this formula:
=INDEX(A1:A13,MATCH(777,F1:F13,0))
To break this formula down, MATCH looks up 777 in the range F1 through F13 as an exact match. It returns that position to INDEX which looks up the matching result in the range A1 through A13 and returns the value October.
The sheet in this tutorial contains a small amount of data for demonstration purposes. But when you have a sheet with hundreds or even thousands of rows or columns, INDEX and MATCH in Google Sheets can help you find the value you need, in any direction, quickly.
For more, look at INDEX and MATCH versus VLOOKUP and XLOOKUP in Excel.
