How to Use the INDEX Function in Excel

The INDEX function in Excel is a value retrieval tool. It returns the value from a specific position in a range, based on the row and column numbers you provide. This makes it especially powerful when combined with the MATCH function for dynamic lookups.

---

What is the INDEX Function?

INDEX lets you get the value from a table or range by specifying its position. You tell it which row and column you want, and it will give you the value that sits there.

It’s more flexible than VLOOKUP or HLOOKUP because it can return values from any direction without rearranging your data.

---

Syntax of the INDEX Function:

There are two main forms of INDEX:

1. Array Form

=INDEX(array, row_num, [column_num])

• array: The range of cells to get the value from.
• row_num: The row number in the array.
• [column_num] (optional): The column number in the array.

2. Reference Form

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

• reference: One or more ranges of cells.
• area_num (optional): Selects which range to use if there are multiple.

---

Where Can We Use the INDEX Function?

The INDEX function is helpful in scenarios such as:

• Dynamic Lookups: Retrieve data based on positions found by MATCH.
• Flexible Data Retrieval: Look up values from any direction.
• Multiple Range Lookups: Pull data from different ranges with one formula.
• Two-Dimensional Tables: Retrieve values by both row and column.
• Building Advanced Dashboards: Combine with MATCH for user-driven results.

---

How to Use the INDEX Function in Different Methods

Here are four common methods to use INDEX in Excel:

---

Method 1: Simple Single-Column Lookup

Get a value from a single column by its row number.

Steps:

1. Enter your data:

A (Names)
John
Maria
Alex

2. In C1, type: =INDEX(A1:A3, 2)
3. Press Enter. The result (Maria) will appear because she’s in row 2 of the range.

---

Method 2: Lookup in a Table by Row and Column

Retrieve a value by specifying both row and column numbers.

Steps:

1. Enter your data:

	A	B	C
1	Name	Age	City
2	John	25	London
3	Maria	30	Paris
4	Alex	28	Tokyo

2. In E1, type: =INDEX(A2:C4, 3, 3)
3. Press Enter. The result (Tokyo) will appear.

---

Method 3: Combining INDEX with MATCH for Dynamic Lookups

Use MATCH to find the position and INDEX to return the value.

Steps:

1. Use the table from Method 2.
2. In G1, type the name (e.g., Maria).
3. In H1, type: =INDEX(B2:B4, MATCH(G1, A2:A4, 0))
4. Press Enter. The result (30) will appear.

---

Method 4: Using INDEX with Multiple Ranges (Reference Form)

You can pick which range to pull from using the area_num argument.

Steps:

1. Enter two ranges of data in different areas.
2. In a formula, use: =INDEX((A1:A3, D1:D3), 2, 1, 2)
3. This will pull from the second range (D1:D3), row 2.

---

Live Example Showing Progress in Each Step

Data Setup:

A (Name)	B (Age)	C (City)
John	25	London
Maria	30	Paris
Alex	28	Tokyo

Goal:
Retrieve values dynamically from the table.

Step 1: Enter the table above in A1:C4.
Step 2: Use INDEX with specific row/column numbers.
Step 3: Combine INDEX with MATCH for flexibility.

Final Table:

Lookup Name	Age	Formula
Maria	30	=INDEX(B2:B4, MATCH(“Maria”, A2:A4, 0))
Alex	28	=INDEX(B2:B4, MATCH(“Alex”, A2:A4, 0))

---

Key Notes About the INDEX Function

• Returns Value, Not Position: Unlike MATCH, INDEX gives you the actual data.
• Can Look Any Direction: Works left, right, up, or down.
• Works with MATCH: Together, they replace VLOOKUP/HLOOKUP for more power.
• Reference Form Is Flexible: Can pull from multiple ranges.
• No Sorting Needed: Works with both sorted and unsorted data.

---

Conclusion

The INDEX function in Excel is a flexible way to pull values from a table or range. Whether you’re retrieving data by row and column numbers or combining it with MATCH for dynamic lookups, INDEX gives you full control.

By learning both array and reference forms, and practicing with MATCH, you can create smarter, more efficient lookup formulas that go beyond the limits of VLOOKUP and HLOOKUP.