Have you ever used INDEX or MATCH functions of excel? These functions actually don’t do much when used in isolation. But when combined, these become the best alternative to VLOOKUP or HLOOKUP and are even more efficient. There are many excel experts who suggest to use INDEX-MATCH instead of VLOOKUP. So, let’s find out how and why we should use these functions in combination as a lookup function.
To be frank, the idea of using two totally unknown functions for something which I am already used to is really bad. And using two functions in combination becomes a little more complicated to understand also. But there are situations when VLOOKUP will fail to retrieve what you are looking for and INDEX-MATCH will work happily. For larger worksheets with thousands of data-rows, using INDEX-MATCH is more efficient as it takes lesser time than VLOOKUP. So finally, I decided to start using it and found it really good. Now I want that you should also try your hand here.
Excel INDEX() Function
INDEX function in excel is really simple to use and understand. Give it the row number and column number and it will throw the value from the defined data range.
Index function syntax
= INDEX(array,row_num,col_num)
If we simplify it in plan English, it will be something like:
=INDEX(data table, Row number of this table where your data is located, Column number of this table where your data is located)
In simplistic use, you will select the data table, put the row number and column number and INDEX will output the value as shown in the example below.
If we define our data table with range C2:D5 and are interested in getting the price of Banana, which is in row number 3 and column number 2 in this table, we can use this index formula.
=INDEX(C2:D5,3,2) which will return 4.
Notes:
- If the array (data table) selected by you contains only one row or one column, the corresponding row_num or col_num argument becomes optional. Thus the formula “=INDEX(D2:D5,3)” will return 4.
- The row_num and col_num values must correspond to a cell in the defined table; otherwise Index formula will throw #N/A error.
If you have followed the Index formula correctly, you will understand that you will probably not use this function in isolation.
Excel MATCH() Function
MATCH function in excel is also very easy to understand. Basically it finds the position of a value in a row or column. So, if you are interested in finding just the relative position of a specific value in a list, you can use MATCH() function.
Match function syntax
= MATCH(lookup_value,lookup_array,[match_type])
In the example above, Match function will look for the position of “Grapes” in the column range C2 to C5.
=MATCH(“Grapes”,C2:C5,0) returns 4.
The match_type argument is optional and just like VLKOOP, here is the option to find exact match or approximate match. Most of the times, you will be interested in getting the exact match, for which you need to pass the value as 0 for [match_type] argument.
It is better to know a few important things about [match_type] argument:
- You can choose from 0, 1 or -1 for this argument. If you leave it blank, excel will use the default value which is 1.
- If you choose 1, MATCH function will find the largest value which is either equal to or less than the lookup value. However, the lookup array must be placed in ascending order like 1,2,3,…….,9,10.
- If you choose -1, MATCH function will find the smallest value which is either equal to or more than the lookup value. Again in this case, you must first sort the lookup array in descending order like 10,9,8,……,2,1.
- Most of the times you will be choosing 0 which will fetch the exact match. Here you don’t need to sort your lookup_array.
Notes:
- You can use uppercase or lowercase in the lookup value if it is text. Match function will find the value irrespective of the case.
- If Match function is unable to find the value you are looking for, it will throw #N/A error.
In real world just like INDEX(), it is less likely that you will be using MATCH() function in isolation. But when you use these two functions together, it becomes really powerful.
INDEX-MATCH as an alternative of VLOOKUP
So, what is actually this Index-match combination going to do for us? The concept is simple; Index will return the value you are looking for by getting a reference number and this reference number will be provided by Match.
The two function put together will look something like this:
=INDEX(The data range, Row number which will be retrieved by MATCH, Column number which will be retrieved by MATCH)
To understand this phenomenon with an example, please download this workbook. Let’s say we are interested to find out the marks of Alex (cell B11). Now, we will use both VLOOKUP function and index-match one by one.
VLOOKUP
As you know the syntax of VLOOKUP is
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
In this case, the VLOOKUP formula will be:
=VLOOKUP(B11,B2:E9, 4, FALSE) which returns 110
If you have difficulty in understanding, you can go through my tutorial on Excel VLOOKP.
INDEX-MATCH
In first step, we will find the relative position of Alex with MATCH function
=MATCH(B11,B2:B9,0) which returns 5
In second step, we will get the marks obtained by Alex which is in the E column at 5th row from top.
=INDEX(E2:E9, 5) which returns 110
But now, we will replace 5 with the MATCH formula. So, the final INDEX-MATCH formula will be:
=INDEX(E2:E9, MATCH(B11, B2:B9,0))
In fact, you can straightaway use both INDEX and MATCH function after a little practice.
OK, we can retrieve the same result with both VLOOKUP and INDEX-MATCH. But why should we go with Index-Match when VlOOKUP can do this task equally well and is relatively easier to use?
Why INDEX-MATCH and not VLOOKUP?
Suppose, in the above example, we want to know the Imp ID of Alex which is in column A. Can you use VLOOKUP here without making any change in data table? The answer is no. This is because VLOOKUP can’t go in left direction.
With INDEX-MATCH, there is no such issue. Use the following formula:
=INDEX(A2:A9, MATCH(B11, B2:B9,0)) which will return A153.
INDEX-MATCH is Faster
There have been several tests to compare the timings for retrieving data with VLOOKUP and INDEX-MATCH. It has been found that in almost all cases, INDEX-MATCH is faster. I don’t want to take you through the process of testing though!
INDEX MATCH with multiple criteria
Unlike VLOOKUP, here you can use two criteria, one for row number and another for column number. That means it is possible to have two way lookup. For understanding purpose, I have created two drop down lists in cell G2 and H2. G2 consists of the names and H2 the fields which value we wish to have in cell I2.
In cell I2, we are using the following INDEX-MATCH formula:
=INDEX(A2:E9, MATCH(G2,B2:B9,0), MATCH(H2,A1:E1,0))
Basically, we are defining the complete data table here. There are two MATCH formulas here. For row_num; we are finding the relative position of selected name in column B and for col_num; we are looking at the relative position of fields in row 1.
Now, you can easily get all the details of the employees by selecting the name in cell G1 and detail field in cell H1. Thus we are using two lookups here which is not available by default in VLOOKUP.
By the way, if you are interested, go through my tutorial on how to create a drop down list in excel. And feel free to ask any questions you may have regarding INDEX-MATCH function in comments section below.