Ravindra BagaleCourses & study guides Track your progress

Excel INDEX Function

By · Last updated:

Chala mitrano — iNDEX is “give me the cell at row r, column c”. Alone it needs numbers; with MATCH it becomes a powerful lookup.

Fictional sample data for learning. Employee roster (Ahmedabad IT / Austin support). Mixed ₹ and $ salaries (fictional).

Purpose

Return the value (or reference) at a given row and column position inside a range. Pair it with MATCH for flexible lookups.

Return value

The value at that position, or a reference you can feed into other formulas.

Syntax

INDEX(array, row_num, [column_num])

Arguments

ArgumentRequired?Description
arrayRequiredThe range or array to pick from.
row_numRequiredWhich row inside array (1 = first row of the range).
column_numOptionalWhich column inside array (1 = first column). Required when array has more than one column.

Version

Excel 2007 and later, Microsoft 365, LibreOffice Calc.

How to use — Why → What → How

Why: Sometimes you know the position (3rd employee, 2nd column). Sometimes MATCH finds the position and INDEX fetches the value. INDEX is the quiet engine behind many interview solutions. Alone it needs a row (and maybe column) number. With MATCH it becomes a lookup that can return left, right, up or down. I...

What: INDEX returns the cell at row_num / column_num inside array. Read the arguments table carefully; each one changes the answer in a specific way.

How — numbered steps:

  1. Select the answer cell.
  2. Type =INDEX( and select the data range.
  3. Type the row number (or a MATCH that returns it).
  4. If needed, type the column number.
  5. Close and press Enter.
  6. Compare your result with the Answers sheet only after you attempt it.
  7. Write one sentence in your own words explaining when you would use this function at work.

Ravindra Bagale's Tip

Friends, row_num is relative to the range you selected — not the sheet row number. INDEX(A2:A10, 1) returns A2.

Ravindra Bagale's Tip

INDEX+MATCH can look left, right, up, or down. That is why interviewers still love it next to XLOOKUP.

Ravindra Bagale's Tip

Friends, after class open a blank sheet and rebuild one INDEX example without looking. That memory test beats re-reading the page.

Download practice file: practice-index.xlsx (includes sheet About this data, practice sheets, and Answers).

Worked examples

1) Value at row 2, column 3

Ahmedabad roster. Pick city for the second employee.

EmpNameCitySalary (₹)
E01Neha ShahAhmedabad72000
E02Omar KhanSurat68000
E03Priya DesaiAhmedabad75000
Formula: =INDEX(A2:D4, 2, 3)

Result: Surat

What it means: Row 2 inside the range is Omar; column 3 is City.

2) Single-column INDEX

Just the salary list. Third salary.

Salary (₹)
72000
68000
75000
Formula: =INDEX(A2:A4, 3)

Result: 75000

What it means: Third cell in the salary column.

3) INDEX + MATCH — salary for Emp E03

Look up by ID without VLOOKUP’s left-column rule.

EmpNameSalary (₹)
E01Neha Shah72000
E02Omar Khan68000
E03Priya Desai75000
Formula: =INDEX(C2:C4, MATCH("E03", A2:A4, 0))

Result: 75000

What it means: MATCH finds position 3; INDEX returns the 3rd salary.

4) Left-side return (name from city)

City is on the right; return Name for Surat.

NameCity
Neha ShahAhmedabad
Omar KhanSurat
Liam BrooksAustin
Formula: =INDEX(A2:A4, MATCH("Surat", B2:B4, 0))

Result: Omar Khan

What it means: MATCH on City, INDEX on Name — classic left lookup.

5) US row — $ salary label

Austin support row stored as text label for demo.

EmpCityPay
E01Ahmedabad₹72,000
E09Austin$4,200
E03Ahmedabad₹75,000
Formula: =INDEX(C2:C4, MATCH("E09", A2:A4, 0))

Result: $4,200

What it means: INDEX returns the pay cell as stored for E09.

6) Classroom recap

Quick check before you close the INDEX lesson.

EmpNameCitySalary (₹)
E01Neha ShahAhmedabad72000
E02Omar KhanSurat68000
E03Priya DesaiAhmedabad75000
Formula: =INDEX(A2:D4, 2, 3)

Result: Surat

What it means: Same pattern as example 1 — rebuild it from memory on the Practice sheet, then check Answers.

About this data. Small demo rows for learning. Real sheets are longer; the same formula pattern scales when columns are clean.

Common errors and fixes

#REF! — row_num or column_num outside the array. Fix: Use numbers within the range size.
#VALUE! — row_num or column_num is not a number (or is blank in some contexts). Fix: Pass a whole number or a MATCH result.
Wrong person — MATCH used approximate match (1 or -1) on unsorted IDs. Fix: Use MATCH type 0 for exact IDs.

Notes and tips

  • INDEX can return a whole row or column when row_num or column_num is 0 (Excel). Keep beginner examples on single cells first.
  • Array form vs reference form — for class, treat INDEX as “pick a cell”.
  • On dynamic arrays, INDEX still works on spilled ranges.
  • INDEX is the quiet engine behind many interview solutions. Alone it needs a row (and maybe column) number. With MATCH it becomes a lookup that can return left, right, up or down. In an Ahmedabad roster, Emp IDs stay in column A while City sits in column C; INDEX+MATCH returns City without VLOOKUP left-key rule. Relative positions matter: INDEX(A2:A10, 1) is A2, not A1. On Microsoft 365 you may still use INDEX on spilled arrays from FILTER.
  • INDEX+MATCH vs XLOOKUP: both can look left. XLOOKUP is shorter on 365/2021. INDEX+MATCH works on older Excel and is still a favourite interview pair.
  • Fictional sample data only — practice amounts and names are made for learning.
  • After you finish the 10 tasks, re-read the errors table once; most class mistakes are listed there.
  • Keep ranges locked with absolute references when you copy formulas down a column of inputs.
  • Say the business question in plain words before you type the equals sign — it prevents wrong arguments.

In real work

INDEX is the quiet engine behind many interview solutions. Alone it needs a row (and maybe column) number. With MATCH it becomes a lookup that can return left, right, up or down. In an Ahmedabad roster, Emp IDs stay in column A while City sits in column C; INDEX+MATCH returns City without VLOOKUP left-key rule. Relative positions matter: INDEX(A2:A10, 1) is A2, not A1. On Microsoft 365 you may still use INDEX on spilled arrays from FILTER.

How it compares

INDEX+MATCH vs XLOOKUP: both can look left. XLOOKUP is shorter on 365/2021. INDEX+MATCH works on older Excel and is still a favourite interview pair.

Interview corner

What does INDEX return?
The value (or reference) at a given position inside a range.
Why pair it with MATCH?
MATCH finds the position; INDEX fetches the value — together they replace many VLOOKUPs.
Is INDEX volatile?
No. That is one reason mentors prefer INDEX over OFFSET on large models.

MATCH, XLOOKUP, VLOOKUP, CHOOSE

Practice tasks (10)

  1. Task 1. Return City for the second row of the roster with INDEX only.
    Hint: row_num 2, column_num for City.
  2. Task 2. Return Salary for Emp E03 using INDEX+MATCH.
    Hint: MATCH on Emp, INDEX on Salary.
  3. Task 3. Return Name for City = Surat (left lookup).
    Hint: MATCH on City column.
  4. Task 4. Return Pay for Emp E09.
    Hint: exact MATCH type 0.
  5. Task 5. Deliberately use row_num 9 on a 3-row range and note the error.
    Hint: watch for #REF!.
  6. Task 6. Explain in one line how INDEX row numbers relate to the sheet.
    Hint: relative to the selected range.
  7. Task 7. Return the first employee name with INDEX.
    Hint: row_num 1.
  8. Task 8. Build INDEX+MATCH that shows Not found via IFNA when Emp is missing.
    Hint: wrap the whole INDEX+MATCH.
  9. Task 9. Return Salary column position 2 only with INDEX (no MATCH).
    Hint: single-column range.
  10. Task 10. Bonus: return City for Liam Brooks.
    Hint: MATCH on Name.

Quick quiz

Q1. INDEX(A2:A10, 1) returns which cell?
Q2. INDEX + MATCH can return a column to the left of the lookup column.
Q3. If array has several columns you usually must supply:
Q4. MATCH type for exact employee IDs is:
Q5. #REF! from INDEX usually means:

Answers: Q1=A2, Q2=True, Q3=row_num and column_num, Q4=0, Q5=Position outside the array (check after you try).

Revision card

Standalone card (no tip or photo strip). QR opens this lesson. Download the PNG and revise offline.

Revision card: INDEX
INDEX revision card