While VLOOKUP is the most famous lookup formula in Google Sheets, it has severe limitations. It can only search from left to right, and if you insert a new column into your database, it completely breaks. For advanced data analysis, professionals rely on the combination of INDEX and MATCH.
Before you can combine them, you must understand the INDEX function on its own. The INDEX function does one specific thing: it returns the value of a cell within a specific range based on its exact row and column coordinates.
In this guide, you will learn the exact syntax of the INDEX function and how to use it to extract precise data points from complex tables.
The Basic INDEX Syntax
The INDEX function requires three arguments, although the third is sometimes optional depending on the shape of your data range.
=INDEX(reference, , [column])- reference: The range of cells (the table) you want to extract data from.
- row: The row number within that specific range (not the spreadsheet row number).
- column: The column number within that specific range.
Use Case 1: Extracting from a 2D Grid
Imagine you have a grid of sales data. The range B2:E5 contains the sales figures. You want to extract the value located in the 3rd row and the 2nd column of that specific block of data.
Click into an empty cell and type:
=INDEX(B2:E5, 3, 2)Google Sheets will look at the block B2:E5. It will count down 3 rows from the top of the block, and then count across 2 columns from the left of the block. It will then output the exact number found at that coordinate intersection.
Use Case 2: Extracting from a Single Column or Row
If your reference range is just a single column (a 1D array), you do not need to provide a column number. The INDEX function will assume you only want to look down.
For example, if you have a list of employee names in column A (A2:A100) and you simply want to extract the 15th name in that list:
=INDEX(A2:A100, 15)Conversely, if your data is arranged horizontally in a single row (e.g., months of the year in B1:M1), you skip the row number by leaving it blank (or putting a 1) and only provide the column number:
=INDEX(B1:M1, 1, 6)This formula would output the value in the 6th cell of that horizontal range.
Extracting Entire Rows or Columns
A powerful, often overlooked feature of INDEX is its ability to extract an entire row or column of data at once, rather than a single cell.
To do this, you place a 0 (zero) in the coordinate you want to extract completely.
- To extract all the data in the 2nd column of your range:
=INDEX(A1:D10, 0, 2) - To extract all the data in the 4th row of your range:
=INDEX(A1:D10, 4, 0)
Google Sheets will automatically “spill” the resulting data downwards or across the adjacent empty cells.
While typing manual coordinates like 3, 2 into the INDEX function is rarely useful on its own, it becomes incredibly powerful when you replace those static numbers with the dynamic MATCH function. Mastering INDEX is the first step to building unbreakable lookup formulas.