The Google Sheets INDEX formula returns a value from a chosen position in a range. For example, with product data in B2:D6, =INDEX(B2:D6,3,2) returns the value in the third row and second column of that range. Pair INDEX with MATCH when you want to find a value by a label instead of counting rows manually.
What INDEX Does in Google Sheets
INDEX retrieves information by position. You give it a range and identify a row, a column, or both; Sheets returns the value at that intersection. The row and column numbers are counted from the top-left corner of the selected range, not from the worksheet’s row and column labels.
That distinction matters. In the range B2:D6, the first row of the range is worksheet row 2, and the first column is worksheet column B. So a row number of 3 refers to worksheet row 4, while a column number of 2 refers to worksheet column C. INDEX is useful for controlled extraction from a table, and it becomes a flexible lookup tool when combined with a function such as MATCH.
INDEX does not search for a word or compare values on its own. It follows the position numbers you provide. If the position needs to be determined from a name, date, product, or heading, MATCH can find that position and supply it to INDEX.
INDEX Syntax and Its Arguments
The general Google Sheets syntax is INDEX(reference, [row], [column]). The reference is the range to read. The optional row argument gives the position within that range, and the optional column argument identifies a column within it. For typical two-dimensional data, provide both row and column.
- reference: The cell range or reference that contains the results you may want.
- row: The relative row position within the reference, starting at 1.
- column: The relative column position within the reference, also starting at 1.
If the reference is a single-column range, you can omit the column argument. If it is a single-row range, you can omit the row argument. The row and column arguments are optional in the function signature, but a useful formula normally supplies enough position information to identify the intended result. A row or column number beyond the size of the selected range produces a reference error.
Google Sheets examples below use commas between arguments, as in the default United States-style locale. A spreadsheet set to a locale that uses different formula separators may require semicolons instead. The function name remains INDEX, but use the separator expected by that sheet’s locale.
Basic INDEX Examples
Suppose B2:D6 contains five product records in three columns: product name, quantity, and unit price. The first row of that selected range is B2:D2, and the third row is B4:D4. With that layout, the following formula selects the quantity from the third record:
=INDEX(B2:D6,3,2)
The result is the value in C4, because C is the second column within B2:D6. If C4 contains 18, the formula returns 18. It does not return the cell at worksheet row 3; it counts three rows down from the start of the range.
For a one-column range containing product names in B2:B6, this formula returns the fourth name in the range:
=INDEX(B2:B6,4)
The result is the value in B5. A single-column example is often easier to read because only a row position is needed. The same positional idea applies to horizontal data: for a row of monthly totals in B2:F2, a column position of 3 refers to D2.
These examples show why choosing the reference carefully is important. If you change the start of the range, the meaning of the row and column numbers changes too. A formula that points to B2:D6 and one that points to B3:D6 are not using the same positional map, even if the ranges overlap.
Using INDEX with MATCH for Lookups
A common use is to combine INDEX with MATCH. MATCH searches a row or column for a target and returns its position. INDEX then uses that position to return the corresponding value from another range. In a MATCH lookup, the final argument 0 requests an exact match, which is generally what you want for text labels, IDs, or specific dates.
Assume A2:A6 contains product names and C2:C6 contains their prices. E2 holds the product name to look up. To return the price from the row where that product appears, use:
=INDEX(C2:C6,MATCH(E2,A2:A6,0))
If E2 contains Mug and Mug is the third item in A2:A6, MATCH returns 3. INDEX then returns the third value in C2:C6, which is C4. If C4 contains 8, the formula returns 8. The two ranges must be aligned: A2:A6 and C2:C6 each have five rows and refer to corresponding records.
This approach is convenient when the lookup value and result are in separate columns. It can also retrieve a value to the left of the lookup column, because INDEX’s result range is chosen independently of the range MATCH searches. That is one reason INDEX with MATCH is a useful alternative to older lookup patterns that require the key to be at the left edge of the lookup table.
If MATCH cannot find the lookup value, it returns an #N/A error, which passes through the formula. Check for spelling differences, extra spaces, or a lookup value that is not present before assuming the INDEX range is wrong. If you decide to replace an error with a message, use an error-handling function deliberately; hiding the error can otherwise make missing data harder to notice.
Two-Way Lookups by Row and Column
INDEX can locate a value using both a row label and a column heading. This is useful for tables where, for example, each row is a region and each column is a quarter. Use one MATCH to identify the row and another to identify the column.
Suppose region names are in A2:A4, quarter headings are in B1:D1, and the numbers are in B2:D4. F2 contains a region name, and G1 contains a quarter heading. The following formula returns the number at their intersection:
=INDEX(B2:D4,MATCH(F2,A2:A4,0),MATCH(G1,B1:D1,0))
For example, if F2 contains South and G1 contains Q2, and South is the second listed region while Q2 is the second heading, INDEX returns the value in C3. If C3 contains 150, the result is 150. Both MATCH functions return positions relative to their own lookup ranges, and those positions correspond to the rows and columns in B2:D4.
For this method to work reliably, keep the labels unique within each lookup direction. If Q2 appears twice in the header range, MATCH returns the first match, which may not identify the column you intended. Likewise, repeated region names can make the row selection ambiguous.
Returning a Whole Row or Column
In Google Sheets, setting the row argument or column argument to 0 can make INDEX return the corresponding full column or row from the reference as an array. This is useful when you want to pass a slice of a table to another function, rather than extract just one cell. The result may fill multiple cells, so the cells where it needs to appear must be clear.
With a table in A2:C6, this formula returns the second column of that range as an array:
=INDEX(A2:C6,0,2)
The result is the values from B2:B6. In a formula cell, the returned array can expand into neighboring rows. If another value is already in the expansion area, Sheets may show an error because the array cannot fit. Clear the obstructing cells or choose a location with enough space. Array-return behavior can also be useful when INDEX is nested inside another function that expects a range-like input.
Do not confuse a zero row or column argument with asking for the first row or first column: position 1 means the first position. Zero has this special whole-row or whole-column behavior in this context. For a single-cell result, use positive position numbers that fall within the dimensions of the reference.
INDEX Compared with Other Lookup Options
INDEX with MATCH is not the only way to look things up in Sheets. The best choice depends on how your table is arranged and whether your goal is to return one result or a set of results.
- VLOOKUP: A familiar option for vertical tables. Its search key must be in the first column of the selected range, so it is less flexible when the return column is to the left of the key.
- XLOOKUP: A newer lookup function available in Google Sheets. It can make common lookups more direct by taking separate lookup and result ranges, so there is no need to calculate a position with MATCH for a basic exact lookup.
- FILTER: Useful when you want all rows or values meeting a condition, not just the first matching position. It can return multiple results when several records qualify.
INDEX with MATCH remains useful when you want to understand and control row and column positions, build a two-way lookup, or work with a table whose result column is not to the right of the key. VLOOKUP can be easier to recognize in an existing spreadsheet, while XLOOKUP may be simpler for a straightforward one-key lookup. FILTER is a better fit when returning multiple qualifying rows is the goal. These functions are not interchangeable in every situation, so match the tool to the desired result.
Common INDEX Problems and How to Fix Them
An INDEX formula is short, but a few common mistakes can produce an error or a plausible yet incorrect value. Check the range dimensions and the positions supplied to the formula before rewriting it.
- #REF! error: A row or column position may be larger than the selected range. Count positions from the range’s top-left cell, not from the worksheet labels.
- #N/A from a combined lookup: MATCH did not find the requested item. Confirm the lookup value exists and that the lookup range contains the intended labels.
- Wrong result, no error: INDEX positions may be valid but point to the wrong record. Verify the range start and confirm that the MATCH lookup range and INDEX result range begin and end on corresponding rows.
- Array expansion error: A formula returning an entire row or column cannot fill cells that already contain data. Clear the output area or move the formula.
- Unexpected position: MATCH defaults to approximate matching if its match-type argument is omitted. Include 0 when an exact match is intended.
It can help to test the MATCH portion on its own in an empty cell. If it returns 3, for instance, that means it found the third position in the lookup range; it does not mean worksheet row 3. Once the position is correct, check that INDEX uses a result range with matching record order. This separates lookup problems from range-reference problems.
Frequently Asked Questions
What does INDEX do in Google Sheets?
INDEX returns the value at a specified row and column position within a reference. It is positional rather than a search function; combine it with MATCH when the position should be determined by a label or value.
How do I use INDEX to return a value by name?
Use MATCH to find the name’s position in a name range, then pass that position to INDEX for the corresponding result range. Use 0 as MATCH’s final argument for an exact match, and keep the two ranges aligned.
Why does my INDEX formula return #REF!?
Usually, one of the requested positions is outside the selected reference. Check that the row number does not exceed the number of rows in the range and that the column number does not exceed its number of columns.
Can INDEX return an entire column in Google Sheets?
Yes. In a two-dimensional reference, a column argument of 0 can return the full column at the specified column position. The array result needs a clear area to expand into if it is placed directly in the sheet.
Is INDEX MATCH better than VLOOKUP?
Neither is always better. INDEX with MATCH can look in one column and return a value from another column on either side, while VLOOKUP is straightforward when the key is in the first column of the selected table. Choose based on table layout and maintainability.
Related
- Windows 10 Excel Keyboard Shortcuts to Work Faster
- Windows 11 Excel Keyboard Shortcuts for Faster Work
- Microsoft Access vs Excel: Which Tool Fits Your Work?
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Conditional Formatting Greyed Out? Excel FixesIf Conditional Formatting is greyed out in Excel for Microsoft 365 on Windows, first check whether you are…
- Excel Date Validation: How to Restrict Dates in CellsExcel date validation restricts what dates users can enter in a cell. In Microsoft Excel for Microsoft 365…
- Goal Seek in Excel: Step-by-Step InstructionsTo use Goal Seek in Excel for Microsoft 365 on Windows, select Data > What-If Analysis > Goal…
- Find Duplicate Entries in an Excel ColumnTo find duplicate entries in an Excel column, select the values and choose Home > Conditional Formatting >…
- Excel Page Break Lines: How to Hide Them in Normal ViewTo hide page break lines in Excel for Microsoft 365 on Windows, go to File > Options >…
- Excel Regression Analysis Output: What Each Statistic MeansExcel regression output summarizes how well a linear model fits your data and how each predictor relates to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.