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.
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.
The =INDEX formula
=INDEX (Data range, Row No., Column No.)
- 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).
The final formula will be
=INDEX ( A1 : I9, 5, 4 )
Thus the output will be the intersection value, that is, 801 miles.