The INDEX function

The INDEX function makes it easy to reference an entire row or column of an array. In this sample chapter from Microsoft Excel Data Analysis and Business Modeling (Office 2021 and Microsoft 365), 7th Edition, you will learn how to use the INDEX function to compute the distance between two cities.

Questions answered in this chapter:

  • 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?

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

Syntax of the INDEX function

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


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