INDEX Function: syntax, examples, mistakes and translation notes
Returns a value at a row and column position.
Syntax
=INDEX(array, row_num, [column_num])
Arguments
array
Range or array containing the result.
row_num
Row position within the array.
column_num
Optional column position within the array.
Worked examples
=INDEX(C2:C100,5)
Returns the fifth item within C2:C100.
=INDEX(B2:D20,MATCH(H2,A2:A20,0),2)
Returns a value using MATCH to find the row position.
Practical tips
- INDEX returns from the range you give it; row and column numbers are relative to that range.
- Pair INDEX with MATCH/XMATCH when the lookup column is not fixed.
- Test MATCH separately before combining functions during debugging.
Common mistakes
- Using worksheet row numbers when the INDEX array starts below row 1.
- Supplying a row or column position outside the array.
Related functions
Frequently asked questions
What does INDEX do?
Returns a value at a row and column position.
Why might a INDEX formula return an unexpected result?
Using worksheet row numbers when the INDEX array starts below row 1. Start by testing the smallest referenced range or condition independently before adding wrappers or fallbacks.
Check the complete formula
Function syntax is only one part of a working workbook. Paste the finished expression into the Formula Checker for structural warnings, the Formatter for readability or the Translator when a supported locale mapping is involved.
Testing note: examples are educational. Spreadsheet versions, locale settings, tables, named ranges and workbook data can change behavior, so validate important formulas with representative data.