In this article we will look at how to use the INDEX MATCH functions in Excel. Index Match function is an alternative to the LOOKUP function VLOOKUP and HLOOKUP. INDEX+MATCH functions are supposed to be faster than VLOOKUP function
Index Match Functions vs VLOOKUP
INDEX MATCH functions combo can perform all task that the VLOOKUP function can do, only in a faster manner.
INDEX+MATCH advantages over VLOOKUP
- INDEX + MATCH can look to the left side or right side in an excel range whereas VLOOKUP can only look to right
- It can work with both horizontal and vertical ranges whereas VLOOKUP can only lookup in vertical ranges
- INDEX + MATCH can work with data sorted in ascending or descending order whereas VLOOKUP can only match data that is sorted in ascending order
- in cases where the data set is large, INDEX + MATCH can be faster than VLOOKUP
First up we will look at the INDEX and MATCH functions individually to understand what each of these functions does. Then we will look at the combo of INDEX+MATCH functions.
The MATCH function returns the position of the search item within a given range of cells.
Syntax of MATCH Function
MATCH(search_item, range, match_type)
- search_item – refers to the value to be searched
- range – refers to the excel range or table where search_item is to be searched. The range can be either a column or a row
- match_type – refers to whether an exact match should be done
Value of exact_match Behavior Details 1 Approximate If this value is used for the match_type argument of the MATCH function, it will find the largest value less than or equal to search_item. For this to work the range must be sorted in ascending order. 0 Exact MATCH finds the first value exactly equal to lookup value. Lookup array does not need to be sorted. -1 Approximate If this value is used for the match_type argument of the MATCH function, it will find the smallest value greater than or equal to search_item. For this to work the range must be sorted in descending order.
Note: – If the match_type is omitted, it defaults to 1. Hence always ensure that the match_type value is 0 (exact match). Also, in most of the cases we would want an exact match so generally the value of 0 is used for match_type.
Sample data that is used for examples in this article.
MATCH function example
Enter the following formula in cell F2
In this example, we are telling the MATCH function to search for the value in C6 (John) in the range C2:C11. Since we want an exact match the third argument for the MATCH function is 0. The value that is returned by the function 4. Which is correct, John is the fourth person on the list.
The INDEX function returns the cell value at the junction of a given row and column number.
Syntax of INDEX function
INDEX(range, row_number, column_number)
- range – this refers to the excel data range or excel table from where data is to be returned
- row_number – the row number from table where the data is to be returned
- column_number – the column number from table where the data is to be returned
INDEX functions has another variation as well. Since the above mentioned syntax is more commonly used, for this article we will be using this version.
INDEX Function example
Enter the following formula in cell F3
In this example we are asking the INDEX function to return the cell value at the intersection of row number 4 and column number 2 from the range $A$2:$D$11. The value returned is “Post Graduate“.
The formula in F3 could also be written as follows with the same result.
The only thing we have changed in this formula is we replaced the row_number argument to a cell reference ‘F3‘ instead of fixed value of 4.
How to use INDEX MATCH in Excel
Lets us now look at how to use the INDEX and MATCH functions together
Enter the formula in cell F4
=INDEX($A$2:$D$11, MATCH(C5, $C$2:$C$11, 0), 2)
he value returned is “Post Graduate“. In this example, the INDEX formula is same as the one we used in the earlier INDEX function example. The only change we have made is for the second argument we used a formula (MATCH(C5, $C$2:$C$11, 0)) instead of cell reference or a fixed value.
INDEX MATCH 2 way lookups
Now lets look at using a function for the third argument of column_number
=INDEX($A$2:$D$11, MATCH(C5,$C$2:$C$11,0), MATCH(B1,$A$1:$D$1,0))
In this example, the INDEX formula is same as the one we used in the previous INDEX MATCH combo function example. The only change we have made is for the third argument we used a formula (MATCH(B1,$A$1:$D$1, 0)) instead of cell reference or a fixed value. Let us understand this function used for column_number argument.
- We want to return the position of the education column in the header row of our data. Hence the first argument we have used is B1. The value in cell B1 is Education.
- The second argument is the range for the header row of our data which is from A1 to D1.
- As we want an exact match, the third argument is 0.
- What this means is we are asking the MATCH function to search for the value Education in the header row of our data and return the column number where the value Education is found.
Refer to the following image to see how the solved examples will look like. The column F contains the formula result and column G contains the formula used to get the results.
The INDEX MATCH functions are very effective in situations where advanced lookups are required, since they do not have the limitations of the VLOOKUP function and also that they are faster than VLOOKUP function.
Example of an ideal situation to use INDEX MATCH
A project schedule file in Excel which could get data using INDEX MATCH function. This data in turn could be used in a Gantt chart to track project scheduling.