MS Excel for Data Analysis on Telegram: “📊 Excel Basics #22

📊 Excel Basics #22 – INDEX() Function

The "INDEX()" function returns the value from a specific position within a range or array. It is one of the most powerful functions for advanced Excel lookups.

📌 What is the INDEX() Function?

"INDEX()" returns a value based on its row number and, when working with a 2D range, its column number.

Syntax:

=INDEX(array, row_num, [column_num])

=INDEX(B2:B5,3)

Result: Finance

Why?

"B2:B5" contains:

1. IT

2. HR

3. Finance

4. Marketing

So "INDEX()" returns the 3rd value.

📌 Example 2 – INDEX() with Rows & Columns

Consider:

Employee | Jan | Feb | Mar

Rahul | 50000 | 55000 | 60000

Priya | 45000 | 50000 | 52000

Amit | 60000 | 65000 | 70000

Formula:

=INDEX(B2:D4,2,3)

Result: 52000

Here:

2 → 2nd row of the selected range

3 → 3rd column of the selected range

📌 Why is INDEX() Important?

"INDEX()" becomes extremely powerful when combined with "MATCH()".

Example:

=INDEX(C2:C5,MATCH(E2,A2:A5,0))

This can find a value dynamically based on another cell.

For example, if E2 = Amit, Excel finds Amit's position and returns the corresponding value from column C.

📌 INDEX() vs VLOOKUP()

VLOOKUP()

• Searches in the first column

• Returns values to the right

• Uses a column index number

INDEX()

• Can return values from any direction

• Doesn't require the lookup column to be the first column

• Works extremely well with "MATCH()"

📌 Real-World Uses

• Retrieve employee information

• Find sales values

• Build dynamic reports

• Create advanced lookup formulas

• Work with large datasets

📌 Common Mistakes

❌ Using an incorrect row number

❌ Using an incorrect column number

❌ Selecting a range that doesn't contain the required data

✅ Best Practices

• Use "INDEX()" with "MATCH()" for flexible lookups

• Use "XLOOKUP()" for simpler modern lookup requirements

• Keep your lookup ranges consistent

• Use exact matching when combining "INDEX()" with "MATCH()"

💡 Remember:

"INDEX()" answers the question:

👉 "Give me the value at this position."

When combined with "MATCH()", it becomes a powerful alternative to traditional lookup functions.

Double Tap ❤️ For More