The INDEX function

Answers to this chapter’s questions

I have a list of distances between U.S. 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 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 cities.

Suppose that 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 in which they play games in Phoenix, Los Angeles, Denver, Dallas, and Chicago. At the conclusion of the road trip, the Seahawks return to Seattle. Can you easily compute how many miles they travel on the trip? As you can see in Figure 4-2, you simply list the cities the Seahawks visit (8-7-5-4-3-2-8) in the order they are visited, 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 Seahawks will travel a total of 7,112 miles on their road trip. Just for fun, I used the INDEX function to show 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, as you can see in Figure 4-1.