The Lookup Alternative

Hey there. Welcome back. In my last post, we discussed about the hugely popular lookup sisters – vlookup and hlookup. Meet them here.

Now, despite their popularity, there are certain things that they simply cannot handle as we had discussed earlier. So, let us understand how to overcome those obstacles. Meet Index and Match.

Let’s understand MATCH first. While lookup functions are used to fetch a value corresponding to another value, match is used to determine whether a value is present in the lookup table and if so, then at what position is it placed.

Excel 3_2

In the above example, we are trying to find ‘EEE’ in an array. Using the match formula, we lookup the value in H3 from the array B2:B16. This gives an answer of 5, which means that are lookup value is positioned in the 5th position of the array.

If the lookup value is not present in the array, excel returns #N/A. This is basically Excel’s way of telling you, hey there, sorry couldn’t find what you were looking for.

Coming back to our problem with the lookup function: What if we want to look for a value which is not in the first row/column? Well, here is how INDEX and MATCH can pair up in helping us in this.

In this example, we are trying to find the value corresponding to the string ‘DDD’. If we breakup the formula:

Excel 3_3

Firstly, excel is solving the inner match function, i.e. it is finding the position of ‘DDD’ in the array C2:C16. This returns 4. Now, it is using the index function, whereby it fetches the 4th value from the array B2:B16, thus accomplishing what we required.

INDEX and MATCH can also be used to search values in a 2-D Array, where both row and column number is unknown.

Excel 3_4

In this example, we are trying to find the value of ‘DDD’ for the month of February. So, excel executes the 2 match functions to find the column number corresponding to Feb and row number corresponding to ‘DDD’ and then uses Index to fetch the appropriate value.

MATCH can also be used in collaboration with the lookup functions if the lookup value is in the first row/column but the column/row number is unknown.

Understanding the Difference

Some people confuse the functionality of MATCH with that of LOOKUPS.  While the latter is used to fetch values corresponding to certain criteria, the former is used to locate a value in an array.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s