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.

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 5^{th} 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:

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 4^{th} 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.

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.

### Like this:

Like Loading...

[…] via The Lookup Alternative — ExcelwithEXCEL […]

LikeLike

[…] there, welcome back. After juggling numbers through functions like vlookup, Index and Match, I thought why not do something fun today. Something which is a bit more visually appealing: […]

LikeLike

You actually make it appear so easy with your presentation but I to find this topic to be actually something that I feel I would never understand. It sort of feels too complicated and extremely huge for me. I am taking a look ahead in your next put up, I will try to get the cling of it!

LikeLike

You really make it seem really easy together with your presentation but I find this matter to be really one thing which I think I’d by no means understand. It sort of feels too complex and extremely huge for me. I’m taking a look forward to your subsequent submit, I’ll attempt to get the hang of it!

LikeLike

Thanks Alex.

I have shifted base of https://finexhub.wordpress.com/category/excel-learn/

For more Excel topics, check the above link.

LikeLike

You really make it seem really easy with your presentation however I find this topic to be really one thing which I think I’d never understand. It sort of feels too complex and extremely broad for me. I am having a look ahead for your subsequent publish, I’ll try to get the grasp of it!

LikeLike

Thanks Pauline.

Do share if you would like to have more clarification on any specific excel topic.

I have shifted base to https://finexhub.wordpress.com/category/excel-learn/

Check the link for more insights on Excel.

LikeLike

Helpful information. Fortunate me I discovered your web site by accident, and I’m stunned why this accident didn’t took place earlier! I bookmarked it.

LikeLike

Thanks Rena.

I have shifted based to https://finexhub.wordpress.com/category/excel-learn/

Check the link for more insights on Excel.

LikeLike