ARRAYS Part 4 - Dynamic INDEXING

Introduction

In the last post we looked at data validation drop-down lists to provide criteria for a multiple INDEX and MATCH look-up array formula, and then formatting the output as a string of text. In this post, we're going to reverse the look-up criteria to an unknown variable - what and when was the most common species visiting in a week - using the MAX function in an INDEX array formula.

Table 1

In table 1 of the DATA tab, we have a series of values for species by day of the week, which is arranged as a two dimensional array (or range), i.e. down a range of rows and across a range of columns:

We are going to find the day of the most common visit by one species, and the name of the species. The first thing to determine is the maximum value in the range B2:H10; using the MAX function across the entire range, we get a value of 77:

We can see that this is the Robin on Thursday in the table above, with the value in row 8 and column E. Note that for clarity, I've populated the table with random values using the RANDBETWEEN function; the real data has many duplicate values and makes these examples less clear to demonstrate.

Dynamic INDEXING

In the last post, we used match to provide row or column numbers for the INDEX function; for this example, where the maximum value could be in any row or column of our table, and will also change as more data is added, we need to use the ROW and COLUMN functions to pass values to INDEX. In the example below, we are going to find the species that corresponds to the maximum value in the whole table:

The INDEX defines the result range as being in column A (A2:A10). To find the correct row, we are going to use the same technique to coerce a numeric result that we used in the last post. Bearing in mind that we are using the IF logical test to ask if any of the data range equals the maximum value in the same range, and that the virtual array will return a 1 (TRUE) or 0 (FALSE) result, we can use the MAX function prior to the logical test to pinpoint the highest value. This works because the maximum value in the virtual array will be 1, and all the other values will calculate to 0. We then use two ROW functions to return the correct row number for the INDEX function. By subtracting the ROW at the top left of the numeric range (B2) from the ROW of the entire numeric range (B2:H10) and then adding 1, we define the virtual range that the array formula will return a value from. 

To find the day of the week, we create a horizontal index of the days of the week (B1:H1): 

We then use the same MAX and IF logical tests as before, and this time subtract the first COLUMN from the entire range of columns and add 1. We now have all three elements of an extended array formula, which we can use with strings of text in a formatted output. Here's the result, which is dynamic and the result will change when more data is entered in the table:

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

In the next post, an introduction to Visual Basic with a quick way to navigate to the next row in a our active tracker!