Chapter 4: The INDEX Function

I have a list of distances between U.S. cities. How do I write a function that returns the distance between, say, Seattle and Miami?
Is there any way I can write a formula that references the entire column containing the distances of each city to Seattle?

Syntax of the INDEX Function

The INDEX function allows you to return the entry in any row and column within a rectangular array of numbers. The most commonly used syntax for the INDEX function is:

INDEX(Array, Row Number, Column Number)

To illustrate, the formula INDEX(A1:D12,2,3) would return the entry in the second row and third column of the array A1:D12. This entry is the one in cell C2.

I have a list of distances between U.S. cities. How do I write a function that returns the distance between, say, Seattle and Miami?

The file Index.xls (see Figure 4-1) contains the distances between eight U.S. cities. The range C10:J17, which contains the distances, is named Distances.

Figure 4-1: You can use the INDEX function to calculate the distance between U.S. cities.

Suppose that you want to enter the distance between Boston and Denver in a cell. Because distances from Boston are listed in the first row of the array named Distances, and distances to Denver are listed in the fourth column of the array, the appropriate formula is INDEX(distances,1,4). We find that Boston and Denver are 1991 miles apart. Similarly, to find the (huge) distance between Seattle and Miami, you would use the formula INDEX(distances,6,8). Seattle and Miami are 3389 miles apart.

Imagine that the Seattle Sonics basketball team is embarking on a road trip in which they play games in Phoenix, Los Angeles, Denver, Dallas, and Chicago. At the conclusion of the road trip, the Sonics return to Seattle. Can we easily compute how many miles they travel on the trip? As you can see in Figure 4-2, we simply list in order the cities the Sonics visit (8-7-5-4-3-2-8), starting and ending in Seattle, and copy from D21 to D26 the formula INDEX(distances,C21,C22). The formula in D21 computes the distance between Seattle and Phoenix (city number 7), the formula in D22 computes the distance between Phoenix and Los Angeles, and so on. The Sonics will travel a total of 7112 miles on their road trip. By the way, I used the INDEX function to show that the Miami Heat log more miles during the NBA season than any other team.

Figure 4-2: Distances for a Seattle Sonics road trip.

Is there any way I can write a formula that references the entire column containing the distances of each city to Seattle?

The INDEX function makes it easy to reference an entire row or column of an array. If we set the row number to 0, the INDEX function references the listed column. If we set the column number to 0, the INDEX function references the listed row. To illustrate, suppose we want to total the distances from each listed city to Seattle. We could enter either of the following formulas:

SUM(INDEX(distances,8,0)) 
SUM(INDEX(distances, 0,8))

The first formula adds up the numbers in the eighth row (row 17) of the Distances array; the second formula adds up the numbers in the eighth column (Column J) of the Distances array. In either case we find the total distance from Seattle to the other cities is 15,221 miles, as you can see in Figure 4-3.