How to use Excel Index Match (the right way)

Introduction

This tutorial will guide you through the effective use of the Excel INDEX and MATCH functions, demonstrating how they provide a powerful alternative to VLOOKUP for complex data lookups. You will learn the fundamentals, advantages, practical applications, and advanced techniques of INDEX and MATCH, allowing you to streamline your data analysis.

Step 1: Understand the INDEX Function

• Purpose: The INDEX function retrieves a value from a specified position in a range or array.

• Syntax:

  INDEX(array, row_num, [column_num])
  

  • array: The range of cells you want to search.
  • row_num: The row number in the array from which to return a value.
  • column_num: (optional) The column number in the array from which to return a value.

• Example: To find the value in the second row, first column of a range A1:B5:

  =INDEX(A1:B5, 2, 1)
  

Step 2: Understand the MATCH Function

• Purpose: The MATCH function finds the position of a specified value within a range.

• 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 the largest value less than or equal, and -1 for the smallest value greater than or equal.

• Example: To find the position of the value "Apple" in the range A1:A5:

  =MATCH("Apple", A1:A5, 0)
  

Step 3: Combine INDEX and MATCH

• Purpose: Using INDEX and MATCH together allows for flexible and dynamic lookups that can search in both directions.

• Syntax:

  =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
  

• Example: To find the price of "Apple" from a list where prices are in B1:B5 and fruits are in A1:A5:

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

Step 4: Perform Two-Way Lookups

• Purpose: Two-way lookups allow you to find an intersection value based on both row and column criteria.
• Example: Suppose you have a matrix where fruits are in A2:A5 (rows) and months in B1:E1 (columns). To find the sales of "Apple" in "January":

  =INDEX(B2:E5, MATCH("Apple", A2:A5, 0), MATCH("January", B1:E1, 0))
  

Step 5: Practical Applications and Tips

• Why Use INDEX and MATCH:

  • They can search both left and right, unlike VLOOKUP.
  • They can perform two-way lookups efficiently.
  • They are less prone to errors when columns are added or removed.

• Practical Tips:

  • Always ensure data ranges are correctly defined.
  • Use named ranges for clarity and easier maintenance.
  • Test your formulas with sample data to ensure they return expected results.

Conclusion

By mastering the INDEX and MATCH functions, you can handle complex data lookups more efficiently than with VLOOKUP. Familiarize yourself with the syntax and practice on real datasets to solidify your understanding. Consider exploring advanced Excel courses for deeper insights and additional techniques.