INDEX Function in Excel: Return the Value at a Row and Column Position

📎 This article includes 1 downloadable practice file ↓

⏱ 1 min readUpdated 28 September 2026

LookupLevel: IntermediateAvailable in: Excel 2007+

In this article
  1. Syntax
  2. Examples
  3. Example 1
  4. Example 2
  5. Power combo
  6. Related functions

INDEX returns the value at a given row (and optional column) of a range. Combined with MATCH it is the most flexible lookup in every Excel version.

Syntax

=INDEX(array, row_num, [column_num])
Argument What it means
array The range to pick from.
row_num Row position inside the range.
column_num Column position (for 2-D ranges).

Examples

Example 1

=INDEX(B2:B100, 5)

The 5th value in the list.

Example 2

=INDEX(C2:C500, MATCH(F2, A2:A500, 0))

Classic INDEX/MATCH lookup.

Power combo

=INDEX(B2:M20, MATCH(P2, A2:A20, 0), MATCH(Q2, B1:M1, 0))

Two-way lookup: row by region, column by month.

MATCH · XLOOKUP · VLOOKUP · OFFSET

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

📎 Practice files for this article

  • 📗
    Lookup scenarios workbookVLOOKUP, HLOOKUP, INDEX/MATCH and XLOOKUP side by side — vertical, horizontal, two-way — plus 6 real failure cases and their fixes, with PASS/FAIL checks.
    ⬇ XLSX · 10 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.

Keep reading

Excel Functions A–ZINDIRECT Function in Excel: Build a Reference From TextExcel Functions A–ZIFS Function in Excel: Several Conditions Without Nested IFsExcel Functions A–ZSUMPRODUCT Function in Excel: Multiply Arrays and Add the Results
✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong