Index function has advantages over Vlookup. Use it in a simple scenario like to calculate distance between 2 cities, when the data is in a table format.

Data

Data

We want to find the distance between any of the 2 cities using the =INDEX formula.

Understanding the objective

Basically, whichever 2 cities you decide, you want the intersection value in the output.

Example, if you want to find the distance between Dallas and Denver, the output will be the intersection which is 801 miles.

Intersecting Value

Intersecting Value

The =INDEX formula

Syntax

=INDEX (Data range, Row No., Column No.)

Understanding

  • Data Range = Select the Entire Range of the data grid.
    A1 : I9
  • Row No. = Since the From location is Dallas, the Row No. will be 4.
    You have to start counting from cell A1, downwards (A1 is the 1st cell in the data range).
  • Column No. = Since the To location is Denver, the Column No. will be 5.
    You have to again start counting from cell A1, to the right (E is the 5th column, thus 5).
Understanding the INDEX function

Understanding the INDEX function

Output

The final formula will be

=INDEX ( A1 : I9, 5, 4 )

Thus the output will be the intersection value, that is, 801 miles.

Final Formula

Final Formula