INDEX and MATCH Functions in Excel

INDEX and MATCH are powerful lookup functions used together as an alternative to VLOOKUP. They provide greater flexibility, allow left-to-right and right-to-left lookups, and work efficiently with large datasets.

INDEX Function

The INDEX Function returns a value from a specified row and column within a range.

Syntax

=INDEX(array,row_num,[column_num])

Arguments

Sample Student Table

INDEX Example

Return the value from Row 3 and Column 2.

=INDEX(B2:B5,3)

Result: Amit

MATCH Function

The MATCH Function returns the position of a value within a range.

Syntax

=MATCH(lookup_value,lookup_array,[match_type])

Arguments

MATCH Example

=MATCH(103,A2:A5,0)

Result: 3

Student ID 103 is found in the 3rd position of the selected range.

Understanding INDEX + MATCH

MATCH first finds the row position, then INDEX returns the value from that position.

Step 1: Find Position

=MATCH(102,A2:A5,0)

Result: 2

Step 2: Return Name

=INDEX(B2:B5,2)

Result: Priya

Combined INDEX + MATCH Formula

Instead of manually finding the row number, MATCH can be placed inside INDEX.

=INDEX(B2:B5,MATCH(102,A2:A5,0))

Result: Priya

Example 1 – Find Student Name

=INDEX(B2:B5,MATCH(103,A2:A5,0))

Result: Amit

Example 2 – Find Course

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

Result: Power BI

Example 3 – Find Fees

=INDEX(D2:D5,MATCH(101,A2:A5,0))

Result: 5000

Why INDEX + MATCH is Better than VLOOKUP

Dynamic Lookup Example

Assume Cell G2 contains Student ID.

=INDEX(B2:B5,MATCH(G2,A2:A5,0))

When the user changes the Student ID in G2, Excel automatically returns the corresponding student name.