» Index Formula vs. Vlookup Formula

What are the advantages of using the Index Formula instead of Vlookup Formula?

How the Index formula calculates and returns results:

The Index formula returns a value from the intersection between the row number and the column number in an Array. For example, in the screenshot below, the amount of Petty Cash for December 2003 is 585, which is the intersection cell of row 7 and column 29.
The Index formula syntax is: =Index(Data, 7, 29), where Data is the defined Name for the sheet.

How the Vlookup formula calculates and returns results:

The Vlookup formula returns a value from the intersection between the found lookup value in the leftmost column and a column index number in the Table Array. For example, in the screenshot above, the Account Number (column A) for Petty Cash is 1091, the column number for December 2003 is 29, and the amount for Account Number 1091 and December 2003 is 585.
The Vlookup formula syntax is: =Vlookup(1091, Data, 29).

So, which one is better to use?

The main difference between the two formulas is that when using Index, there is no need to organize the data table into a special format – you simply find the intersection between a row and a column. When using Vlookup, though, you always need to move the lookup column to the left side of the data table.

Use the Match formula to find the row, column and index column number when using both Vlookup & Index formulas
The Match formula returns the cell number where the value is found in a horizontal or vertical range. For example, look at row 3 in the screenshot below. December 2003 is in cell number 29 in row 1 (the Name defined for the list is Row1), see the screenshot on the previous page.
In row 7 in the screenshot below, two Match formulas are nested in an Index formula. The first calculates the row number where the text Petty Cash is found (row 7) and the next one calculates the column number for December 2003 (number 29).

Screenshot // Index Formula vs. Vlookup Formula