The INDEX function

Answers to this chapter’s questions

I have a list of distances between US cities. How do I write a function that returns the distance between, for example, Seattle and Miami?

The file named INDEX.xlsx (see Figure 4-1) contains the distances between eight US 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 cities.

Suppose you want to enter in a cell the distance between Boston and Denver. 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). The results show that Boston and Denver are 1,991 miles apart. Similarly, to find the (much longer) distance between Seattle and Miami, you would use the formula=INDEX(distances,6,8). Seattle and Miami are 3,389 miles apart.

Imagine that the Seattle Seahawks NFL team is embarking on a road trip during which they will play games in Phoenix, Los Angeles, Denver, Dallas, and Chicago. At the conclusion of the road trip, the Seahawks will return to Seattle. Can you easily compute how many miles they will travel on the trip? As you can see in Figure 4-2, you simply list the numbers in the spreadsheet that correspond with the cities that the Seahawks will visit (8, 7, 5, 4, 3, 2, 8), one number per row, in the order the cities will be visited, starting and ending in Seattle. Then, copy the formula INDEX(distances,C21,C22) from D21 to D26. 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 Seahawks will travel a total of 7,112 miles on their road trip. (Just for fun, I used the INDEX function to determine that the Miami Heat travel more miles during the NBA season than any other team.)

Figure 4-2 Distances for a Seattle Seahawks road trip.

Is there a way I can write a formula that references the entire column containing the distances between each city and Seattle?

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

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

The first formula totals the numbers in the eighth row (row 17) of the distances array; the second formula totals the numbers in the eighth column (column J) of the distances array. In either case, you find that the total distance from Seattle to the other cities is 15,221 miles. (Refer to Figure 4-1.)