INDEX Function in Excel and Google Sheets
Purpose
Return the value at a specific row and column position within a range
Return value
The value at the intersection of row_num and col_num within array
INDEX is the retrieval half of the MATCH Formula">INDEX MATCH combination that experienced spreadsheet users reach for whenever VLOOKUP is not flexible enough. On its own, INDEX simply retrieves a value from a specified position in a range — give it which range, which row number, and optionally which column number, and it returns whatever is stored at that intersection. That is a straightforward and useful capability. But the real reason to learn INDEX is what happens when you pair it with MATCH. MATCH is a search function that finds the row number where a value appears in a column. INDEX takes that row number and retrieves the corresponding data from a completely separate column. Together they recreate VLOOKUP's functionality with none of its limitations: the search column and return column can be in any order, the formula survives column insertions without breaking, and complex multi-criteria lookups become possible by using arrays inside MATCH.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=INDEX(array, row_num, [col_num])
Arguments
| Argument | Required | Description |
|---|---|---|
| array | Required | The range or array to retrieve from. Can be a single column, single row, or a multi-column range. If array is a single column, col_num can be omitted. |
| row_num | Required | The row position within array to return from. Row 1 is the first row of the array — not row 1 of the spreadsheet. Use MATCH to calculate this dynamically from a lookup value. |
| col_num | Optional | The column position within array. Required when array spans multiple columns. Omit when array is a single column. Use 0 to return an entire row as an array. |
How it works
INDEX(array, row_num) retrieves the value at the position row_num within the range array. For a single-column range this is all you need — row_num counts from the first row of the array, not from row 1 of the spreadsheet. If array starts at row 5, then row_num 1 returns the value in the 5th row of the spreadsheet. For a multi-column range, add the col_num argument to specify which column to return from, counting from the left edge of the range starting at 1. The most important application of INDEX is the MATCH Formula">INDEX MATCH combination: =INDEX(return_column, MATCH(lookup_value, search_column, 0)). MATCH finds the position number and INDEX uses it to retrieve the corresponding value. Both the search_column and return_column are independent range references — they can be anywhere in the spreadsheet without restriction. Wrap the whole formula in IFERROR to handle cases where MATCH cannot find the lookup value: =IFERROR(INDEX(D:D, MATCH(A2, B:B, 0)), "Not found").
Examples
1
Retrieve a product name from a specific row number in a table.
| A | B | C | |
|---|---|---|---|
| 1 | Row | Product Name | Price |
| 2 | 1 | Wireless Mouse | 29.99 |
| 3 | 2 | USB Keyboard | 45.99 |
| 4 | 3 | Monitor Stand | 89.50 |
| 5 | 4 | Laptop Bag | 59.99 |
| 6 | 5 | HDMI Cable | 12.99 |
| 7 | 6 | Webcam HD | 79.00 |
| 8 | 7 | Desk Lamp | 34.50 |
| 9 | 8 | Headset Pro | 129.00 |
Row 2: Laptop Bag
— Row 4 of B2:B9 is Laptop Bag — INDEX returns the value at that position.
Row numbering in INDEX counts from the first row of your array, not from the spreadsheet row number. Row 1 of B2:B9 is cell B2, row 4 is B5.
2
Use INDEX MATCH to look up a department by employee ID — the classic combination.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Find ID | Employee ID | Name | Department | Result |
| 2 | EMP-004 | EMP-001 | Alice Chen | Engineering | |
| 3 | EMP-002 | Bob Smith | Marketing | ||
| 4 | EMP-003 | Carol Jones | Sales | ||
| 5 | EMP-004 | David Kim | Finance | Finance | |
| 6 | EMP-005 | Eve Taylor | Engineering | ||
| 7 | EMP-006 | Frank Brown | Sales |
Row 2: Finance
— MATCH finds EMP-004 at position 4 in B2:B8. INDEX returns the value at position 4 in D2:D8 which is Finance.
MATCH returns 4 (the row position of EMP-004), then INDEX(D2:D8,4) returns Finance. The search column (B) and return column (D) can be in any order — unlike VLOOKUP.
Common use cases
1. Retrieve a salary or value from a column to the LEFT of the search column — a direction VLOOKUP cannot handle
=INDEX(A:A,MATCH(D2,C:C,0))
2. Two-way lookup returning the value at the intersection of a matching row and column header
=INDEX(B2:E10,MATCH(H2,A2:A10,0),MATCH(I2,B1:E1,0))
3. Find and return the name of the employee who achieved the highest sales figure in the dataset
=INDEX(A:A,MATCH(MAX(C:C),C:C,0))
4. Return a dynamically selected column from a table based on a header name chosen from a dropdown
=INDEX(B:E,MATCH(H2,A:A,0),MATCH(I1,B1:E1,0))
5. Look up multiple criteria simultaneously using an array formula inside MATCH
=INDEX(D:D,MATCH(A2&B2,B:B&C:C,0))
Common errors
#REF! — row or column number out of range
row_num or col_num is larger than the dimensions of array. Requesting row 15 from a 10-row array returns #REF! because row 15 does not exist in the array.
Fix: Verify the dimensions of array using ROWS() and COLUMNS(). Make sure row_num and col_num are within bounds. If using MATCH to generate the row number, confirm the search column is the same length as the return column.
Returns the value from the wrong row
Row counting in INDEX starts from the first row of the array argument, not from row 1 of the spreadsheet. If array is B5:B20, INDEX(B5:B20,1) returns the value in B5 — spreadsheet row 5 — not the value in row 1 of the sheet.
Fix: Remember that INDEX row numbers are always relative to the top of the array, not the sheet. If you expect row 1 of the sheet, use INDEX starting from row 1 of the sheet.
#N/A propagating from MATCH inside INDEX
When MATCH cannot find the lookup value, it returns #N/A. This #N/A is passed as the row_num argument to INDEX which then also returns #N/A.
Fix: Test MATCH independently first to confirm it finds the expected values. Wrap the complete formula in IFERROR: =IFERROR(INDEX(D:D,MATCH(A2,B:B,0)),"Not found").
Tips and variations
Use INDEX MATCH as the standard solution for lookups that need left-column returns
VLOOKUP restricts you to returning values from columns to the right of the search column. INDEX MATCH removes this restriction entirely — the return column can be to the left, to the right, or anywhere. This makes it the universal lookup tool when VLOOKUP runs out of flexibility.
=INDEX(A:A,MATCH(D2,C:C,0))
Always wrap INDEX MATCH in IFERROR for production reports
When the lookup value is not guaranteed to exist in the search column, MATCH returns #N/A and INDEX propagates it. In a shared report this creates visible error cells that look broken. IFERROR replaces the error with a blank or a descriptive message.
=IFERROR(INDEX(D:D,MATCH(A2,B:B,0)),"Not found")
Use XMATCH with INDEX in Excel 365 for cleaner, more modern syntax
XMATCH is the modern replacement for MATCH with better default settings and additional matching modes. It defaults to exact match without requiring 0 as the third argument and supports wildcard and approximate matching more flexibly. Combined with INDEX it produces the same results as INDEX MATCH with slightly cleaner syntax.
=INDEX(D:D,XMATCH(A2,B:B))
Excel vs Google Sheets
Excel vs Google Sheets
INDEX works identically in Excel and Google Sheets. Same syntax, same row and column counting, same behaviour. MATCH Formula">INDEX MATCH formulas copy between the two without any changes.
Related reading
Related Formulas
Frequently asked questions
INDEX returns the value at a specific row and column position within a range or array. You supply the range to look in, the row number to retrieve from, and optionally a column number for multi-column ranges. On its own INDEX is straightforward but rarely the final solution. Its real power comes from combining it with MATCH, which calculates the row number dynamically by searching for a value — together they form the INDEX MATCH combination that is the most flexible lookup tool in Excel.
VLOOKUP requires the search column to be the very first column of table_array and can only return values from columns to the right of that search column. INDEX MATCH has no such restrictions. The search column and return column are completely independent references that can be in any order — INDEX can return from a column that is to the left of the search column, which VLOOKUP cannot. INDEX MATCH is also more resilient to structural changes because it uses direct column references rather than a counted column number, so inserting a new column into the table never breaks the formula.
MATCH searches a column for a value and returns the row number where it appears. INDEX uses that row number to retrieve the corresponding value from a different column. =INDEX(C:C,MATCH(A2,B:B,0)) first runs MATCH(A2,B:B,0) which finds the position of A2 in column B — say it is row 4 — then INDEX(C:C,4) returns the value in row 4 of column C. The result is functionally the same as VLOOKUP but without the restriction that column B must be to the left of column C.
Yes — omitting one of the position arguments tells INDEX to return the entire row or column for that dimension. INDEX(A1:E10,3,0) returns the entire third row of the range as an array. INDEX(A1:E10,0,2) returns the entire second column as an array. This is useful when you need to reference a dynamic range in formulas like SUM, AVERAGE, or MAX where the specific row or column to use depends on a lookup result.
Yes — INDEX works identically in Google Sheets and Excel with the same syntax, the same row and column counting starting from 1 at the top-left of the array, and the same behaviour when omitting arguments to return entire rows or columns. INDEX MATCH formulas copy between the two applications without any changes. In Google Sheets and Excel 365, XLOOKUP provides a simpler alternative for straightforward lookups that do not require the full flexibility of INDEX MATCH.