INDEX MATCH

Explanation

INDEX MATCH is a lookup function which is considered by many as superior to the old-but-gold VLOOKUP function

INDEX MATCH, while being considered “one” function, is actually a combination of two Excel functions – INDEX and MATCH

Example

 

Why would you want to use INDEX MATCH instead of VLOOKUP?

Well, there are certain issues that make VLOOKUP a really problematic function in some cases:

  • Have you ever tried to use VLOOKUP when the value to return in your table array was before the lookup column? If you did try, you would know that this is impossible using VLOOKUP without manipulating the data:

  • VLOOKUP doesn’t respond well when one of the columns within the table array is deleted, or a new column is added. This may cause a wrong result or even a #REF error!
  • Have you ever tried to VLOOKUP a table array of 50 columns? It is pretty easy to get lost when working with arrays of this size.
  • INDEX MATCH’s performance is considered superior to VLOOKUP’s performance, especially in large tables.

Should you always prefer using INDEX MATCH over VLOOKUP?

Well, the answer is NO. As much as we love INDEX MATCH, its logic is sometimes counter-intuitive, and some users have very hard time understanding how this functions work together.

For most users, VLOOKUP works fine and is much easier to understand. However, when facing complex and large tables – INDEX MATCH is definitely a better option.

Syntax

So, after you’ve been convinced that INDEX MATCH is absolutely the best function(s) in Excel, here’s how we use it:

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

Let’s understand the idea behind this combination of functions:

First, we use MATCH function to lookup a value (lookup_value) within a column (lookup_array). The function returns the location of the matched value. We use 0 in [match_type] to make sure we get an exact match!

Then, we use the position received from the MATCH function, and use this position within the INDEX function, to return the corresponding value from our results column (array).

Here’s a nice example of how INDEX MATCH works:

Practice INDEX MATCH

And now, lets get cooking! See if you can master the powers of INDEX MATCH: