INDEX, MATCH Excel Practice Questions with Solutions

Introduction

INDEX and MATCH are powerful Excel functions for finding and returning data from tables. They are especially useful when your lookup data is not arranged in a simple vertical format. In this chapter, you will practice INDEX, MATCH, and combined INDEX-MATCH formulas using employee, product, student, sales, and business data. The questions become more practical as you move through the chapter. INDEX, MATCH Excel Practice questions with solutions to help you understand the concepts.

Question 1: Find an Employee’s Department Using INDEX-MATCH

Problem Statement

Use the Employee ID to find the department of each employee.

Excel Data

Search Data

Excel Solution

In the Department column, enter:

=INDEX($C$2:$C$6,MATCH(A9,$A$2:$A$6,0))

Copy the formula down.

Expected Output

Concepts Covered

• INDEX
• MATCH
• Exact matching
• Lookup using an employee ID

Question 2: Find a Product Price Using INDEX-MATCH

Problem Statement

Use the Product ID to find the corresponding product price.

Excel Data

Search Data

Excel Solution

In the Price column, enter:

=INDEX($D$2:$D$6,MATCH(A9,$A$2:$A$6,0))

Copy the formula down.

Expected Output

MATCH finds the position of the Product ID, while INDEX returns the price from the same position.

Question 3: Return a Value Using INDEX and a Known Position

Problem Statement

Sometimes you already know the position of an item in a list. Use INDEX to return the corresponding employee name.

Excel Data

Search Data

Excel Solution

In the Employee column, enter:

=INDEX($B$2:$B$6,A9)

Copy the formula down.

Expected Output

Concepts Covered

• INDEX
• Position-based lookup
• Returning values from a range

Question 4: Find the Position of a Product Using MATCH

Problem Statement

Use MATCH to find the position of each Product ID in the product list.

Excel Data

Search Data

Excel Solution

In the Position column, enter:

=MATCH(A9,$A$2:$A$6,0)

Copy the formula down.

Expected Output

MATCH returns the relative position of the matching value.

Question 5: Find Student Marks Using INDEX-MATCH in Excel

Problem Statement

Use the student’s Roll Number to find their Maths marks.

Excel Data

Search Data

Excel Solution

Enter:

=INDEX($C$2:$C$6,MATCH(A9,$A$2:$A$6,0))

Copy the formula down.

Expected Output

Question 6: Perform a Two-Way INDEX-MATCH Lookup

Problem Statement

A company stores monthly sales for each salesperson. Find the sales amount using both the salesperson’s name and the month.

Excel Data

Search Data

Excel Solution

Enter:

=INDEX($B$2:$E$6,MATCH(A9,$A$2:$A$6,0),MATCH(B9,$B$1:$E$1,0))

Copy the formula down.

Expected Output

Concepts Covered

This is called a two-way lookup.

The first MATCH finds the salesperson’s row.

The second MATCH finds the month’s column.

INDEX returns the value where the two positions intersect.

Question 7: Handle Missing Values with INDEX-MATCH

Problem Statement

Find an employee’s salary using the Employee ID. If the ID does not exist, display "Employee Not Found" instead of an error.

Excel Data

Search Data

Excel Solution

Enter:

=IFERROR(INDEX($C$2:$C$6,MATCH(A9,$A$2:$A$6,0)),"Employee Not Found")

Copy the formula down.

Expected Output

Concepts Covered

• IFERROR
• INDEX
• MATCH
• Error handling

Question 8: Find the Salesperson with the Highest Sales

Problem Statement

Find:

1. The highest sales amount.
2. The salesperson who achieved that amount.

Excel Data

Excel Solution

Find the highest sales amount:

=MAX(B2:B6)

Then find the salesperson:

=INDEX(A2:A6,MATCH(MAX(B2:B6),B2:B6,0))

Expected Output

Concepts Covered

• MAX
• INDEX
• MATCH
• Lookup using a calculated result

Question 9: Find the Cheapest Product Using INDEX-MATCH

Problem Statement

Find the product with the lowest price and return both the product name and its category.

Excel Data

Excel Solution

Find the lowest price:

=MIN(C2:C6)

Find the product:

=INDEX(A2:A6,MATCH(MIN(C2:C6),C2:C6,0))

Find the category:

=INDEX(B2:B6,MATCH(MIN(C2:C6),C2:C6,0))

Expected Output

This technique is useful when you need to return information associated with the minimum or maximum value.

Question 10: Build a Two-Way Product Price Lookup

Problem Statement

A company has different prices for the same product in different cities.

Use the Product and City to find the correct price.

Excel Data

Search Data

Excel Solution

Enter:

=INDEX($B$2:$E$6,MATCH(A9,$A$2:$A$6,0),MATCH(B9,$B$1:$E$1,0))

Copy the formula down.

Expected Output

This is a practical two-way lookup that combines two MATCH functions with INDEX.

Key Takeaways

• INDEX returns a value from a specific position in a range.
• MATCH finds the position of a value inside a range.
• INDEX-MATCH combines both functions to create flexible lookups.
• MATCH(...,0) performs an exact match.
• INDEX-MATCH can return values from columns located to the left or right of the lookup column.
• Two-way INDEX-MATCH can search by both row and column.
• IFERROR can make lookup formulas easier to use when a value does not exist.
• INDEX-MATCH can be combined with MAX and MIN to find information associated with the highest or lowest value.
• These formulas are useful for employee records, inventory, sales reports, student marks, price lists, and dashboards.

FAQs

1. What does INDEX do in Excel?

INDEX returns a value from a specified position within a range.

Example:

=INDEX(A2:A6,3)

This returns the third value from the range.

2. What does MATCH do in Excel?

MATCH finds the position of a value in a range.

Example:

=MATCH("Amit",A2:A6,0)

The 0 specifies an exact match.

3. What is INDEX-MATCH in Excel?

INDEX-MATCH combines INDEX and MATCH to find a value based on another value.

Example:

=INDEX(C2:C10,MATCH(A2,A2:A10,0))

MATCH finds the position, and INDEX returns the corresponding value.

4. Why is 0 used in MATCH?

0 tells Excel to find an exact match.

=MATCH(A2,B2:B10,0)

This is commonly used with employee IDs, product IDs, names, and other exact lookup values.

5. What is a two-way INDEX-MATCH lookup?

A two-way lookup uses one MATCH to identify a row and another MATCH to identify a column.

For example, you can find a specific salesperson’s sales for a specific month.

6. Can INDEX-MATCH look to the left?

Yes. Unlike traditional VLOOKUP, INDEX-MATCH can return a value from a range that is positioned to the left of the lookup range.

7. How can I handle #N/A with INDEX-MATCH?

Use IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(A2,A2:A10,0)),"Not Found")

8. Can INDEX-MATCH find the highest value?

Yes. For example:

=INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10,0))

This returns the name associated with the highest value.

9. Can INDEX-MATCH find the lowest value?

Yes. For example:

=INDEX(A2:A10,MATCH(MIN(B2:B10),B2:B10,0))

This returns the name associated with the lowest value.

10. Is INDEX-MATCH still useful when XLOOKUP is available?

Yes. XLOOKUP provides a modern lookup approach, while understanding INDEX and MATCH is useful for working with existing Excel files, older Excel versions, and more complex lookup logic.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar