The INDEX function in Excel returns a value according to its position in a table.

VLOOKUP vs INDEX

The difference between VLOOKUP and INDEX functions is not apparent to many people. The difference is quite simple.

• VLOOKUP searches by value (employee name, product name, product code, ...)
• INDEX searches by position (for instance, returns the value in column 2 and row 5)

There is no difference between the two functions in terms of search speed.

Presentation of the function INDEX

The function INDEX in Excel works with three arguments

1. The range of cells containing the data you want to reference
2. The row position of the value you want to return.
3. And / Or, the column position of the value you want to return.

The order of parameters is very important. Often, users reverse the settings of the row and the column, and as a result, the function does not return the correct result.

Example: INDEX to return the month name

• Let's say you have the list of the months' names on a worksheet.
• What is month #6?
• It's easy to find the answer using the INDEX function.

But of course, we can replace the value 6 with the reference of a cell, like this

Now, If the data source is in a row, the function's writing is slightly different.

• The second argument is = 1 (search on the first row)
• The third argument is the position

Function to return the position

So, INDEX expects a rank to return a value in your reference table. However, if you have a large list of values in your table, such as customers' names, how do you locate the position of clients like Smith or Garcia?

This is where the function MATCH will help you convert a value to a position. For instance, we select a month, and the MATCH function returns the rank.

=MATCH(E1,C1:C7)

Function INDEX with 2 dimensions

The INDEX function is powerful with a 2-dimensional array. Most Excel users use it to return the value of a cell inside a grid of data. For example, we want to return the distance between 2 cities (the explanation to build such a cross table in this article)

So here, we must convert the name of the cities as a position with the function MATCH

=MATCH(B10,A2:A8,0)

Then, we will use this information in the INDEX function to return the distance between the cities

=INDEX(B2:H8,C10,C11)

Your selection mustn't include the headers.

Related Articles

• How do you use the MATCH function in Excel?
• How do you use the VLOOKUP function?
• Why does VLOOKUP return #N/A?
• Function XLOOKUP in Excel
• 3D Formula in Excel to build your formula through many worksheets