INDEX-MATCH
Introduction
INDEX-MATCH is another of Excel’s lookup functions. I love this formula as it’s more flexible and powerful than VLOOKUP. It is a combination of two formulas: INDEX and MATCH.
Why?
The lookup value can be in a column, row or both!
The lookup value can be stored anywhere in the table or range, unlike VLOOKUP where it has to be in the first column of the table / range
Once you’ve mastered it, you’ll rarely use VLOOKUP!
Excel Formula =INDEX(array,MATCH(lookup_value,lookup_array,[match_type])
*Any component of the formula in square brackets means it's optional.
Formula Made Easy
Example
Mad Hatter Chocolatier is a chocolate store which sells chocolates with a variety of flavours. Every month, the finance team receives raw data (RAW DATA tab) that shows how many orders were made per shop.
To make things easier, the finance team would like to categorize the shop name by shop types (type). They can't use VLOOKUP as the lookup_value, "Shop Name" is not in the first column of their table_array, STOCKISTS tab. So they use INDEX-MATCH.
Applying my example to the "Formula Made Easy" table:
Formula Applied
Putting the above references into the Excel formula, it will be:
=INDEX(Stockists!A:A,MATCH(G2,Stockists!B:B,0))
I usually do the MATCH formula first as I find it more logical to know what am I looking for, where is it and then where is the result (INDEX). If you’re not sure where which column goes (INDEX or MATCH) in that order try doing the MATCH formula first! ☺
Download file to follow along here: Mad Hatter Chocolatier
To read my post on VLOOKUP, click here: VLOOKUP
留言