Excel Tip of the Week #390

Hello all and welcome back to the Excel Tip of the Week. This week we have a Creator level post in which we’re taking a deeper dive into the INDEX function, covering some of its lesser-known qualities, and examining how to use these for some common Excel tasks.

If you are looking for a refresher on the basics of INDEX and its common home in replacing VLOOKUP via the INDEX MATCH combination, check out TOTW #201.

Finding items in a 2D range

The first and simplest fact about INDEX is that it can work with either a 1D range (a single row or single column), or with a 2D range (an area with multiple rows / columns).

For example, we can find the intersection of a target row and column using a INDEX and two MATCH:

The formula:

=INDEX($C$3:$F$6, MATCH(C10, $B$3:$B$6, 0), MATCH(C11, $C$2:$F$2, 0))

The first MATCH is used for the row number, and the second identifies the column number.

Finding items in multiple 2D ranges

If you’ve ever looked at the tooltip while writing an INDEX, you might have noticed that it’s a bit unusual:

Almost uniquely among Excel functions, there’s a second row, featuring an alternate syntax for the function. This exists because INDEX can also pick from several different non-contiguous ranges to do its thing. The syntax is a little odd but it’s easiest to look at an example. We’ll return to our same data as before, but this time we’ll introduce a second sales centre:

Here’s the formula in full:

=INDEX(($C$3:$F$6, $I$3:$L$6), MATCH(C11, $B$3:$B$6, 0), MATCH(C12, $C$2:$F$2, 0), C10)

The key differences are that, firstly, the “range” input is now multiple areas, in a comma separated list and within a set of parentheses. Secondly, there’s an extra input to the formula – right at the end, a number which indicates which of the ranges in that set of parentheses the formula should apply to. Technically speaking these ranges don’t have to be the same size, but practically speaking this version of INDEX only really makes sense when they are (note that we’re using MATCH on just one of the two tables here and relying on the fact that the two are laid out identically). It’s important to note that the ranges must all be on the same sheet.

0 to return an entire row/column

When making a 2D INDEX, you can use a 0 for either the row or column input to return the entire row/column. This usually is used inside of other formulas when you want to refer to a part of a larger array based on a dynamic element. So let’s look at this example of a cleaning rota:

We could use a 2D INDEX to find out, say, whose job it was to do the laundry on Wednesdays. But what about if we wanted to use a formula to find out which day it was Sandeep’s turn to do the hoovering? If we always wanted to look at hoovering, an INDEX MATCH on the day header row and the Hoover row would do the trick. But what if we want to be able to switch both the person we’re looking for and the row we’re hunting in? To do that, we end up doing an INDEX MATCH INDEX MATCH!

Here’s that formula:

=INDEX($C$2:$F$2, MATCH(C11, INDEX($C$3:$F$6, MATCH(C10, $B$3:$B$6, 0), 0), 0))

The bold portion is an INDEX that uses MATCH to identify which row we want, and a 0 in the “column” position to return all the columns – so we get the entire row fed into the outer INDEX MATCH. The same basic approach applies to situations like this:

Here we use a 0 in the row input to return all the rows, i.e. the entire column.

INDEX returns a reference At first glance, it might look like INDEX returns the value of whatever cell it identifies – after all, that’s what you see in the cell. But under the hood, what INDEX actually does is return a reference to that cell – such as A1 – which then in turn usually spits out the value. But this also means that INDEX can appear anywhere that a cell reference could appear – for example, on one side of a colon when defining a range.

Here’s an example of a total-to-target formula:

The formula:

=SUM(C3:INDEX(ProductSales[Sales], MATCH(F4,ProductSales[Day], 0))) The output of the INDEX is a reference to the sales figure for the 18th, which then creates a range along with the starting reference, C3, that is used in the SUM.

Of course you can also use an INDEX on both sides of a colon: