INDEX: Return Values by Position (2025)
Quick Answer: INDEX returns value from array position: =INDEX(A1:C10, 5, 2) gets row 5, column 2. Combine with MATCH for flexible lookups: =INDEX(Prices, MATCH(Product, Names, 0)). More powerful than VLOOKUP. Works any direction.
What is INDEX?
INDEX returns a value from a specific position in an array. Think of it like GPS coordinates - you specify row and column numbers, and INDEX retrieves the value at that intersection.
Visual Example:
| A | B | C | |
|---|---|---|---|
| 1 | Product | Price | Stock |
| 2 | Apple | $1.50 | 100 |
| 3 | Banana | $0.75 | 200 |
| 4 | Orange | $2.00 | 150 |
=INDEX(A1:C4, 3, 2)
Returns: $0.75 (row 3, column 2 of array A1:C4)
INDEX vs VLOOKUP:
VLOOKUP
Searches for value, returns from right
=VLOOKUP("Banana", A:C, 2, 0)
• Searches column A
• Returns column 2
• Only looks right
INDEX
Returns value by position
=INDEX(A1:C4, 3, 2)
• Direct row/column
• Any direction
• Needs position number
INDEX + MATCH = Power Combo:
The Problem with INDEX alone:
You need to know exact row/column numbers. What if data moves?
The Solution: INDEX + MATCH:
=INDEX(Prices, MATCH("Banana", Products, 0))MATCH finds "Banana" position → INDEX returns corresponding price
Two Forms of INDEX:
Array Form (Most Common)
=INDEX(array, row_num, [column_num])
Returns value from single array
Reference Form (Advanced)
=INDEX((array1, array2), row_num, col_num, area_num)
Returns value from multiple areas
💡 Common Use Cases
- • Flexible lookups: INDEX/MATCH replaces VLOOKUP with left-looking ability
- • Two-way lookups: Find value by both row and column criteria
- • Return entire rows/columns: Omit row or column parameter
- • Dynamic ranges: Build references that adjust automatically
- • Data extraction: Pull specific data points from large arrays
INDEX Syntax
=INDEX(array, row_num, [column_num])
Arguments
array (required)
Range of cells or array constant from which to retrieve value
Range: A1:C10 or Prices[Column]
Array constant: {1,2,3;4,5,6}
row_num (required)
Row number in array to return value from (0 returns entire column)
3 or MATCH(value, range, 0)column_num (optional)
Column number in array (0 returns entire row, omit for 1-column arrays)
2 or MATCH(header, headers, 0)Common Patterns
Single Column Array
=INDEX(A1:A10, 5)
Returns 5th value from column A (column_num omitted)
Two-Dimensional Array
=INDEX(A1:D10, 5, 3)
Returns row 5, column 3 from A1:D10
INDEX/MATCH Combo
=INDEX(B:B, MATCH(A2, A:A, 0))
Find A2 in column A, return corresponding B value
Two-Way Lookup
=INDEX(Data, MATCH(Row, Rows, 0), MATCH(Col, Cols, 0))
Find value by both row and column criteria
Return Entire Row
=INDEX(A1:D10, 5, 0)
Returns all columns from row 5 (array result)
Special Cases
Row_num = 0 (Return Entire Column)
Returns entire 3rd column (C1:C10) as array
Column_num = 0 (Return Entire Row)
Returns entire 5th row (A5:D5) as array
Both = 0 (Invalid)
Returns #VALUE! error - can't omit both
⚠️ Important Notes
- • 1-based indexing: First row is 1, not 0 (unlike programming arrays)
- • Relative position: Row/column numbers relative to array, not sheet
- • Out of bounds: Row/column beyond array size returns #REF! error
- • Array results: Using 0 for row or column returns array (requires Excel 365 or Ctrl+Shift+Enter)
- • Combined with MATCH: Most powerful when MATCH provides row/column numbers dynamically
- • Performance: Fast even on large arrays (100k+ rows)
12+ INDEX Examples
Basic INDEX
1. Return Single Value
=INDEX(A1:C10, 5, 2)
Returns value from row 5, column 2 of range
2. Single Column (No Column Number)
=INDEX(B:B, 10)
Returns 10th value from column B
3. Last Value in Column
=INDEX(A:A, COUNTA(A:A))
Returns last non-empty value in column A
INDEX + MATCH Combos
4. Simple Lookup (Replace VLOOKUP)
=INDEX(Prices, MATCH(A2, Products, 0))
Find product in Products column, return price
5. Left Lookup (VLOOKUP Can't Do This)
=INDEX(A:A, MATCH(D2, C:C, 0))
Search column C, return corresponding column A value (left of lookup)
6. Approximate Match
=INDEX(Grades, MATCH(A2, Breakpoints, 1))
Find grade bracket (85 finds "80-89 = B" if sorted)
Two-Way Lookups
7. Matrix Lookup (Row + Column)
=INDEX(B2:E10, MATCH(A2, A2:A10, 0), MATCH(B1, B1:E1, 0))
Find value by matching both row header and column header
8. Sales by Month and Product
=INDEX(SalesData, MATCH(Product, Products, 0), MATCH(Month, Months, 0))
Cross-reference: Find sales for specific product and month
Advanced Techniques
9. Return Entire Row
=INDEX(A1:D100, 50, 0)
Returns entire row 50 as array (A50:D50)
10. Return Entire Column
=INDEX(A1:D100, 0, 3)
Returns entire column 3 as array (C1:C100)
11. Dynamic Column Selection
=INDEX(Data, ROW(), MATCH(ColumnName, Headers, 0))
Flexible column reference based on header name
12. Multiple Criteria (Array Formula)
=INDEX(Results, MATCH(1, (Crit1=Range1)*(Crit2=Range2), 0))
Find first row where both criteria match (Ctrl+Shift+Enter for legacy Excel)
🎯 Pro Tips
- • INDEX/MATCH vs VLOOKUP: INDEX/MATCH more flexible, works any direction
- • Column insertion safe: Unlike VLOOKUP, adding columns doesn't break formula
- • Performance: INDEX/MATCH slightly faster than VLOOKUP on large datasets
- • Array results: Use 0 for row/column to return entire row/column (Excel 365)
- • Named ranges: Use table references for clearer formulas
- • Error handling: Wrap in IFERROR to handle #REF! and #N/A errors
Frequently Asked Questions
When should I use INDEX instead of VLOOKUP?
Use INDEX/MATCH when: (1) Return column is LEFT of lookup column, (2) You insert/delete columns frequently (INDEX/MATCH won't break), (3) You need faster performance on large datasets, (4) You want more flexible lookups. VLOOKUP is simpler for basic right-side lookups in static tables.
Why does INDEX return #REF! error?
#REF! means row or column number exceeds array bounds. If array is A1:C10 (10 rows, 3 columns), =INDEX(A1:C10, 15, 2) returns #REF! (row 15 doesn't exist). Check: (1) Row/column numbers are within array size, (2) MATCH isn't returning #N/A (use IFERROR), (3) Array reference is correct.
How do I use INDEX for two-way lookups?
Use two MATCH functions: =INDEX(data_range, MATCH(row_value, row_headers, 0), MATCH(col_value, col_headers, 0)). First MATCH finds row position, second finds column position. INDEX returns intersection. Example: Find sales for Product X in Month Y from sales matrix.
Can INDEX return multiple values?
Yes, in Excel 365. Use 0 for row or column: =INDEX(A1:D10, 5, 0) returns entire row 5. =INDEX(A1:D10, 0, 3) returns entire column 3. Results spill automatically. In older Excel, use Ctrl+Shift+Enter for array formula, or select multiple cells first.
What's the difference between INDEX array form and reference form?
Array form (common): =INDEX(A1:C10, 5, 2) returns from single range. Reference form (rare): =INDEX((A1:A10, C1:C10), 5, 1, 2) returns from 2nd area. Reference form uses multiple separate ranges (area_num selects which). 99% of uses need array form only.
Why is my INDEX/MATCH returning wrong results?
Common causes: (1) MATCH range and INDEX range don't align (different row counts), (2) MATCH using wrong match_type (0=exact, 1=sorted ascending), (3) Extra spaces in lookup values (use TRIM), (4) Data types don't match (text vs numbers), (5) Array not sorted for approximate match.