ARRAYS Part 2 - INDEX and MATCH

Introduction

In the previous post we looked at the SUMPRODUCT function, and then modified it to act as a SUM and a COUNTIFS function in the two tables of data we had created earlier. In this post, we're looking at the INDEX and MATCH functions, using them to look up values, and then combining these with part of the SUMPRODUCT function to perform multiple criteria look-ups.

Table 3

For this post, I've created a third table which lists the weight of the male and female of each species in the list:

INDEX

The INDEX function returns the value in a cell at the intersection of a row and column number in a given array or range of data. In the first formula below, the array is cells A1 to C21, and we are asking for the value in the cell in row 5 of column 1:

In the second formula, I have defined the array as a single column range (A1:A21), and then specified row 5. Although the function syntax is (ARRAY, ROW NUMBER, COLUMN NUMBER), indexing a single dimension array causes Excel to accept this as the second condition, and then takes the user defined row number as the first condition. 

MATCH

The MATCH function returns the row or column number of a specified value in a horizontal or vertical range:

This function can be used to supply the row condition to an INDEX function, and, combined, the two provide a look-up solution that replaces VLOOKUP and HLOOKUP and is not limited to finding values to the right or below the criteria in a range.

INDEX and MATCH 

In the example below, you can see the evolution of INDEX, then MATCH and then INDEX and MATCH combined:

The first part of the formula creates an array of data from cells A1 to B21. The MATCH function then finds the row number in the range A1 to A21 that matches the criteria in cell H9, which evalutes to row 5. Following this, we specify 0 for an exact match, and then finally specify that we want the corresponding value in column 2 of the array. Note that in this instance, I selected more than one column in the INDEX stage, so it is necessary to specify the column.

In the example below, I've increased the array to take in column C, and specified 3 as the column to return the value from:

Multiple criteria look-up 

In the previous post, we used SUMPRODUCT to find the number of species for each day of the week based on a range of logical tests that we coerced into an array of 1s and 0s. We can use the same principal with INDEX and MATCH to perform a look-up based on more than one criteria, and the value returned can be text or numbers (SUMPRODUCT can only return a numeric, calculated value). In the following example, the look-up criteria for the MATCH function is "1"; going back to the virtual array calculation that we coerced SUMPRODUCT to create, the calculated output where all the criteria evaluated as TRUE was "1", whereas all other calculations resulted in "0". Using this logic, and entering the formula as an array formula, the MATCH function will find the row where all the arguments are TRUE, and evaluate to "1" and supply this as the first condition in the INDEX function. The column has been set by providing a single column reference, which becomes the second condition:

The key to this formula is that it calculates across arrays of data; this formula will fail unless it's entered as an array formula by pressing CONTROL+SHIFT+ENTER (CSE). Note the curled parentheses that surround the entire formula ("{}"); these are generated by the CSE entry and are not typed around the formula.  In the first version above, MATCH criteria is set at "1", which will be the result of the array calculations of the logical tests; the following calculation uses the multiplication
operator. Notice the similarity of the interior of this formula to the SUMPRODUCT function that we used before:

 In the second example, the MATCH criteria is set at "2", and the operator is an addition; as we have two conditions that will evaluate to "1", it follows that the MATCH we are looking for is "1+1=2". This is simply to demonstrate how the arrays of data are handled, and it would be preferable not to use addition or subtraction as they are not necessary. Using division between the arrays and setting the MATCH criteria at "1" gives the same result as multiplication. In the final example below, you can see that you can add any number of calculations after the arrays you are calculating; as long as the output of all the calculations evaluates to 1, then the result will be identical:

  

This formula can be increased to include as many criteria as you require and will work on horizontal and vertical arrays, or a mixture of the two in a two dimensional array. As mentioned earlier, this formula can return a result from anywhere in a range of data, and is not restricted to looking to the right or below the criteria.

I hope you enjoyed this post - please get in touch if you have any questions or need any help.

In the next post, we are going to use Data Validation to create dynamic drop down lists as the criteria for this formula!