Function: INDEX()

🔍 Purpose

The INDEX() function returns a value from a specific row and column of a given range.

It is extremely powerful when used with MATCH, CHOOSE, FILTER, and dynamic dashboards.

You can use INDEX() to:

• Pull data from a specific location

• Build flexible lookup models

• Create dynamic reports

• Replace VLOOKUP/HLOOKUP in advanced models

🧮 Syntax

1. Array Form

INDEX(array, row_num, [column_num])

2. Reference Form

INDEX(reference, row_num, [column_num], [area_num])

🔧 Parameters

• array / reference → The range from which to return the value

• row_num → Row number inside the range

• column_num → Column number inside the range

• area_num → Used when selecting between multiple ranges (advanced)

📘 Explanation

Think of INDEX() as picking a cell from a box.

Example:
=INDEX(A2:C6, 3, 2)
Means → From the table A2:C6, give the value at 3rd row, 2nd column.

🧩 Example 1: Basic INDEX()

Example Table

Task: Return the Course of the 3rd student

Formula:

=INDEX(A2:D6, 3, 3)

Output → Web Design

🧩 Example 2: INDEX() with Dynamic Column Selection

Goal: Return Fees of row 5.

=INDEX(A2:D6, 5, 4)

Output → 20000

🧩 Example 3: INDEX() + MATCH() (Advanced Lookup)

Goal: Find Fees for Priya Sen.

MATCH to find row number

=MATCH("Priya Sen", B2:B6, 0)

Result → 2

INDEX to return Fees

=INDEX(D2:D6, MATCH("Priya Sen", B2:B6, 0))

Output → 6500

🧩 Example 4: INDEX() with Multi-Area Lookup (Reference Form)

Table Set (Two Ranges)

Range 1—Course Fees

Range 2—Course Duration

Goal: Return duration of “Web Design”.

Formula:

=INDEX((B2:B6, D2:D6), MATCH("Web Design", A2:A6, 0), 1, 2)

Output → 4

Explanation:

• Area 1 → Fees

• Area 2 → Duration

• We select area 2, row = MATCH(“Web Design”), column = 1

🧩 Example 5: INDEX() for 2-Way Lookup (Row + Column)

Sales Table (4 × 5)

Goal: Find February sales of Bag.

Row:
=MATCH("Bag", A2:A6, 0) → 3
Column:
=MATCH("Feb", B1:D1, 0) → 2

Final formula:

=INDEX(B2:D6, MATCH("Bag", A2:A6, 0), MATCH("Feb", B1:D1, 0))

Output → 2200

📚 When Should You Use INDEX()?

INDEX() is one of the core functions in advanced Excel.

🛠️ Student Practice Projects (INDEX-Based)

Project 1 (Easy): Employee Salary Finder

Table

Task:

1. Return salary of employee in row 4 using INDEX.

2. Return salary of Kunal using INDEX + MATCH.

Project 2 (Medium): Product Price Finder

Table

Tasks:

1. Find stock of Shoes.

2. Create formula to return any column using a dropdown and INDEX + MATCH.

Project 3 (Hard): Dynamic Student Report Switcher

Table

Tasks:

1. Create a dropdown: Name / Marks / Grade

2. Use INDEX() to return the selected data for Roll Number = 3

3. Build a 2-way lookup model with INDEX + MATCH

Project 4 (Harder): 2-Way Dashboard Lookup

Table

Tasks:

1. Create dropdowns for City and Quarter

2. Use INDEX + MATCH + MATCH to return sales

3. Add conditional formatting for top 3 results

Project 5 (Hardest): Multi-Area INDEX() Selection

You have two separate tables:

Table 1: Fees

| Course | Fee (₹) | Duration | Center |
| Excel | 5000 | 1 | Kolkata |
| Tally | 8000 | 2 | Delhi |
| Photoshop | 6500 | 1 | Mumbai |
| Web Design | 15000 | 4 | Chennai |
| Digital Marketing | 20000 | 3 | Bangalore |

Table 2: Batch Timings

| Course | Morning | Afternoon | Evening |
| Excel | 10 AM | 2 PM | 6 PM |
| Tally | 9 AM | 1 PM | 5 PM |
| Photoshop | 11 AM | 3 PM | 7 PM |
| Web Design | 10 AM | 4 PM | 8 PM |
| DM | 9 AM | 12 PM | 6 PM |

Tasks:

1. Use INDEX(reference) to select between Fee table and Timing table.

2. Let user choose Course, Area No (1 or 2), Column.

3. Fetch the correct value dynamically.