Have you ever needed to look up data both horizontally and vertically? Or finding that your VLOOKUP functions are taking an age to update? Combining the INDEX and MATCH functions in Excel may be just the solution you require. The 2 functions are rarely used on their own but once combined can produce some fantastic results. In this article we will look at the 2 functions separately before combining them.
The match function finds the position of text in a row or column of data. For example, is the data in the 3rd row or the 5th column?
Structure of the MATCH Function
=MATCH(lookup value, lookup array, match type)
The lookup value is what we are looking up, the lookup array is the range of cells we are looking the data up in and can be either cells in 1 row or in 1 column. The match type determines whether we are looking for exact matches or a nearest match. For an exact match 0 is entered, 1 for a nearest match less than the figure we are looking for and -1 for a nearest match greater than the figure we are looking for.
In the example below the MATCH function is used to find the position of the £25,000 from cell G15 in the loan amounts figures in cells B3:B13 and will only return an answer if it’s an exact match. In this case the result would be 5.
The index function finds the result that is in a certain row and column of the data.
Structure of the INDEX function
=INDEX(array, row num, col num)
The array is the range of data where the lookup is to be performed. The Row num is the row number that contains the data, the col num is the column number that contains the data. Row number is required and must be filled in but column number is optional.
In the example below we are looking at the data in cells G3:G31 and finding the data in the 5th row and 3rd column. The result would be £197.70.
Combining INDEXand MATCH functions
By combining these functions the calculation becomes much more useful. We can now look for specific data in a table and find that information.
Structure of the combined INDEX and MATCH functions
=INDEX(array, MATCH(lookup value, lookup array, match type), MATCH(lookup value, lookup array, match type))
Match is used to replace the row num and col num from the original INDEX function for the added flexibility required.
In the example below the first MATCH function will find £25,000 in the 5th row of the data. The second match function will find 15 in the 3rd column of the data. Combining that with INDEX now finds the answer in the 5th row and 3rd column which is £197.70. This time the combines functions have allowed us to look for something meaningful rather than a generic row and column number.
Extra advantages of using INDEX and MATCH
INDEX and MATCH can often be used in place of a VLOOKUP and although the function looks more complicated beacuse we are using 2 functions instead of 1 there are some key advantages.
- The function can be used to lookup data to the left or the right of the lookup column. With the VLOOKUP function the data that is looked up must be in the first column of the table
- INDEX and MATCH work more efficiently than VLOOKUP. For small sets of data this will not be obvious but as the size of the data or the complexity of calculations being performed increases it can take a while for the calculations to update. This will be less of an issue with INDEX and MATCH.