INDEX MATCH FUNCTION

Introduction

In this article we will look at INDEX MATCH Excel function. Its a combination of two functions INDEX and MATCH. INDEX MATCH function in Excel is used to perform advanced lookup and retrieval of data from a table or range. Its primary purpose is to find a specific value within a range and then return a corresponding value from another column or range, based on certain criteria.

Arguments for INDEX MATCH

=INDEX(array,MATCH(lookup_value,lookup_array,[match_type])

• array: This is the range from which you want to retrieve data
• lookup_value: The value that we are looking for
• lookup_array: The range in which we want to find the value
• match_type: The options for this argument are: -1 , 0 or 1. 0 for exact match, -1 and 1 for approximate match

Key differences between VLOOKUP and INDEX MATCH

• VLOOKUP is primarily designed for vertical lookup, meaning it searches for a value in the leftmost column of a table and returns a corresponding value from a specified column. INDEX MATCH on the other hand can perform both vertical and horizontal lookups, making it more versatile.
• VLOOKUP requires you to specify the column number from which to return the result, INDEX MATCH on the other hand does not have this limitation.
• With VLOOKUP, the lookup value must always be in the leftmost column of the table you’re searching. INDEX MATCH does not have this restriction and can look up values in any column.

Advantages of using INDEX MATCH

• INDEX MATCH can perform both vertical and horizontal lookups, allowing you to retrieve data from tables or ranges in any direction.
• INDEX MATCH is more flexible since it does not require you to specify a static column number as VLOOKUP does. hence the formula will still work correctly even if the order of columns in your data changes.

Conclusion

INDEX MATCH function is a valuable addition to any Data Analyst’s toolkit, enabling them to tackle complex data retrieval tasks with ease and precision. Please take a moment to review my prior article, where I discussed the VLOOKUP function

Happy learning data nerds!