INDEX MATCH > VLOOKUP
The INDEX MATCH function is one of Excel's most powerful functions. The friend of the much-used VLOOKUP, INDEX MATCH allows users to look up values in a table based off rows and columns. Unlike VLOOKUP, it can be used on rows, columns, or both at the same time.
It is important to realize that INDEX MATCH is not actually a standalone function, but rather a combination of Excel's INDEX and MATCH functions. Read on to find out how to use them together to activate one of the most powerful functions.
Let’s go through an example. Here we have a table with sample HR data.
In the table, if you notice, Employee ID is the only unique key which can be used to lookup other information.
Let's use INDEX MATCH function to find out who is Employee # 7268 and what department does he/she belong to.
=INDEX(range, MATCH(lookup_value, lookup_range, match_type))
Let's take a closer look at what's going on here. First, we call INDEX on a range of numbers from which we would like to look up a given value. Then, we use MATCH to tell Excel what we are looking for (lookup_value) and a range of cells being searched (lookup_range).
Using the same function, we can find what department does Bill(#7268) belong to?
After all this, you may be wondering why we even bother using INDEX MATCH. Can't we use VLOOKUP instead?
No! Here are a few reasons you might want to use INDEX MATCH instead:
You can lookup backwards. VLOOKUP only allows you to look up from columns that are in front of your starting point (right side). Using INDEX MATCH, you can pull from any column you want to.
You can safely insert columns. With VLOOKUP, if you insert a column in between the start of your table and the column you want to reference, your formula will break, the column_index_number within your VLOOKUP won't update. On the other hand, INDEX MATCH safely updates no matter where you insert columns.
You don't need to remember separate formulas for VLOOKUP and HLOOKUP.
If you are confused or can't remember how to use INDEX MATCH, try this:
=INDEX ( Column I want to return value from , MATCH ( Lookup Value , Column I want to Lookup against , Enter “0” ))
