4 Mins. to MASTER Excel INDEX & MATCH formulas!

Introduction

This tutorial will guide you through mastering Excel's INDEX and MATCH functions, which provide a powerful alternative to VLOOKUP. By the end of this guide, you'll be able to perform advanced lookups in Excel, efficiently retrieving data from different tables and ranges.

Step 1: Understanding the INDEX Function

The INDEX function returns a value from a specified position in a range. Here's how to use it:

• Syntax: INDEX(array, row_num, [column_num])
  • array: The range of cells or array from which you want to retrieve data.
  • row_num: The row in the array from which to return a value.
  • column_num (optional): The column in the array from which to return a value.

Practical Example:

1. Suppose you have a table of sales data in cells A1:C5.
2. To retrieve the sales amount from the second row and third column, use:

   =INDEX(A1:C5, 2, 3)
   

Step 2: Understanding the MATCH Function

The MATCH function finds the position of a specified value within a given range. Here's how to use it:

• Syntax: MATCH(lookup_value, lookup_array, [match_type])
  • lookup_value: The value you want to find.
  • lookup_array: The range of cells to search.
  • match_type (optional): 0 for an exact match, 1 for less than, and -1 for greater than.

Practical Example:

1. To find the position of "Product A" in a list in column A (A1:A5), use:

   =MATCH("Product A", A1:A5, 0)
   

Step 3: Combining INDEX and MATCH for Powerful Lookups

To perform a lookup with both functions, nest the MATCH function within the INDEX function.

Example:

1. You want to find the sales amount for "Product A" from the sales table.
2. Use the following formula:

   =INDEX(B1:B5, MATCH("Product A", A1:A5, 0))
   

   • This retrieves the value from the sales column (B1:B5) corresponding to "Product A".

Step 4: Performing Lookups to the Left

Unlike VLOOKUP, INDEX & MATCH can perform lookups to the left of the reference column.

Example:

1. If you want to find the product name based on a sales amount in column B, use:

   =INDEX(A1:A5, MATCH(200, B1:B5, 0))
   

   • This retrieves the product name associated with the sales amount of 200.

Conclusion

You now have the tools to leverage INDEX and MATCH for advanced data retrieval in Excel. These functions offer more flexibility than VLOOKUP, allowing you to look up values in any direction. Practice these techniques with real-world data to enhance your Excel skills further. For further learning, consider exploring Excel courses or more advanced tutorials.