INDEX() and MATCH() in Advanced Excel

INDEX() and MATCH() are two of the most powerful and versatile functions in Excel. When used together, they can replace VLOOKUP() or HLOOKUP() and offer more flexibility. While VLOOKUP() and HLOOKUP() are limited in many ways, INDEX() and MATCH() allow you to search for values in any direction, making them ideal for complex lookups.

INDEX() Function

The INDEX() function returns a value from a specified position in a range or array. It allows you to retrieve data from anywhere in the table, not just the first column or row.

INDEX() Syntax

Syntax:

INDEX(array, row_num, [column_num])
  • array: The range or array from which to retrieve the value.
  • row_num: The row number in the array from which to retrieve the value.
  • [column_num]: Optional. The column number in the array from which to retrieve the value (useful for 2D arrays).

Real-Time Example of INDEX()

Scenario: You have a list of products and their corresponding prices, and you want to find the price of a specific product.

        A            B
        1   Product    Price
        2   Laptop     1000
        3   Tablet     500
        4   Phone      300
        5   Monitor    150

Steps:

  1. In cell D2, enter the product name you want to look up (e.g., "Tablet").
  2. In cell E2, enter the following formula to find the price of the specified product:
    3. =INDEX(B2:B5, MATCH(D2, A2:A5, 0))
  3. Explanation: The MATCH() function finds the position of the product name from D2 ("Tablet") in the range A2:A5. Then, the INDEX() function uses that position to return the corresponding value from the range B2:B5 (price).
  4. If you enter "Tablet" in cell D2, the result in E2 will be "500".

MATCH() Function

The MATCH() function searches for a specified value in a range and returns the relative position of that value. Unlike VLOOKUP(), which can only search in the first column, MATCH() can search for values anywhere in a row or column.

MATCH() Syntax

Syntax:

MATCH(lookup_value, lookup_array, [match_type])
  • lookup_value: The value to search for.
  • lookup_array: The range or array to search within.
  • [match_type]: Optional. 1 for approximate match (default), 0 for exact match, and -1 for reverse search.

Real-Time Example of MATCH()

Scenario: You want to find the position of a product in a list based on its name.

        A
        1   Product
        2   Laptop
        3   Tablet
        4   Phone
        5   Monitor

Steps:

  1. In cell B2, enter the product name you want to search for (e.g., "Phone").
  2. In cell B3, enter the following formula to find the position of the specified product:
    3. =MATCH(B2, A2:A5, 0)
  3. Explanation: The MATCH() function searches for the value in B2 ("Phone") within the range A2:A5 and returns its relative position. The "0" in the formula specifies that we want an exact match.
  4. If you enter "Phone" in cell B2, the result in B3 will be "4" because "Phone" is in the 4th row of the range.

Using INDEX() and MATCH() Together

By combining INDEX() and MATCH(), you can look up data in any column or row, providing more flexibility compared to VLOOKUP() or HLOOKUP(). This combination is especially useful when you need to search in multiple directions or lookup columns to the left of the data.

Real-Time Example of INDEX() and MATCH() Combination

Scenario: You have a table of employee names, their departments, and their salaries, and you want to find the salary of a specific employee.

        A            B           C
        1   Employee   Department  Salary
        2   John       HR          5000
        3   Alice      Finance      6000
        4   Mark       IT          7000
        5   Emma       Marketing   4000

Steps:

  1. In cell E2, enter the employee name you want to search for (e.g., "Mark").
  2. In cell F2, enter the following formula to find the salary of the employee:
    3. =INDEX(C2:C5, MATCH(E2, A2:A5, 0))
  3. Explanation: The MATCH() function searches for the employee name from E2 ("Mark") within the range A2:A5 and returns the position. Then, the INDEX() function uses that position to return the corresponding salary from the range C2:C5.
  4. If you enter "Mark" in cell E2, the result in F2 will be "7000".

Benefits of Using INDEX() and MATCH() Over VLOOKUP()

  • Flexibility: INDEX() and MATCH() can search in any direction, while VLOOKUP() can only search vertically in the leftmost column.
  • Dynamic Range: INDEX() and MATCH() allow you to dynamically change the lookup array, while VLOOKUP() requires a fixed column index.
  • Efficiency: INDEX() and MATCH() are generally faster than VLOOKUP(), especially in large datasets.

Conclusion

The INDEX() and MATCH() functions are extremely powerful tools in Excel, providing more flexibility and efficiency than traditional lookup functions like VLOOKUP() or HLOOKUP(). By combining these two functions, you can perform complex lookups in any direction, making it easier to work with large datasets and perform more advanced analyses.




Advertisement