How to use the INDEX function in Excel?

How to use the INDEX function in Excel?
Last Updated on 09/11/2023
Reading time: 3 minutes

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

VLOOKUP vs INDEX

For many people, the difference between the functions VLOOKUP or INDEX is not obvious. In fact, 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 works with three arguments

  1. Identify the range of cells containing the data you want to reference

    This range can be a single cell, a row, a column, or a block of cells

  2. Determine the row position of the value you want to return.

    The row position is the number of the row containing the value, starting with 1 for the top row.

  3. Determine the column position of the value you want to return.

    The column position is the number of the column containing the value

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

INDEX returns the distance between cities

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?
  • Well, with the function INDEX, it's easy to find the answer.

=INDEX(A2:A13,6)

INDEX returns the month name in function of its position in the list

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

=INDEX(A2:A13,C1)

The position in the reference table is returned by a cell reference

Now, If the data source is in a row, the writing of the function is a little bit different.

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

=INDEX(C3:I3,1,D8)

INDEX with the reference table in horizontal orientation

Function to return the position

So, INDEX expects a rank to return a value in your reference table. But if you have a huge list of values on your table, like customers' names, how to find the position of the clients Smith or Garcia?

This is where the function MATCH will help you to convert a value to a position 😉

Here, for instance, we select a month and the function MATCH returns the rank.

=MATCH(E1,C1:C7)

MATCH returns the position of the days

Function INDEX with 2 dimensions

The INDEX function is very powerful with a 2-dimensional array. Like that, you can return the value of a cell inside a grid of data. This is how most Excel users use this function.

For example, we want to return the distance between 2 cities (the explanation to build such a cross table in this article)

Distance between US cities

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

=MATCH(B10,A2:A8,0)

MATCH returns the position of a city name in the list in column A

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

=INDEX(B2:H8,C10,C11)

It is compulsory that your selection does NOT include the headers.

INDEX returns the distance between cities

Leave a Reply

Your email address will not be published. Required fields are marked *

How to use the INDEX function in Excel?

Reading time: 3 minutes
Last Updated on 09/11/2023

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

VLOOKUP vs INDEX

For many people, the difference between the functions VLOOKUP or INDEX is not obvious. In fact, 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 works with three arguments

  1. Identify the range of cells containing the data you want to reference

    This range can be a single cell, a row, a column, or a block of cells

  2. Determine the row position of the value you want to return.

    The row position is the number of the row containing the value, starting with 1 for the top row.

  3. Determine the column position of the value you want to return.

    The column position is the number of the column containing the value

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

INDEX returns the distance between cities

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?
  • Well, with the function INDEX, it's easy to find the answer.

=INDEX(A2:A13,6)

INDEX returns the month name in function of its position in the list

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

=INDEX(A2:A13,C1)

The position in the reference table is returned by a cell reference

Now, If the data source is in a row, the writing of the function is a little bit different.

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

=INDEX(C3:I3,1,D8)

INDEX with the reference table in horizontal orientation

Function to return the position

So, INDEX expects a rank to return a value in your reference table. But if you have a huge list of values on your table, like customers' names, how to find the position of the clients Smith or Garcia?

This is where the function MATCH will help you to convert a value to a position 😉

Here, for instance, we select a month and the function MATCH returns the rank.

=MATCH(E1,C1:C7)

MATCH returns the position of the days

Function INDEX with 2 dimensions

The INDEX function is very powerful with a 2-dimensional array. Like that, you can return the value of a cell inside a grid of data. This is how most Excel users use this function.

For example, we want to return the distance between 2 cities (the explanation to build such a cross table in this article)

Distance between US cities

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

=MATCH(B10,A2:A8,0)

MATCH returns the position of a city name in the list in column A

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

=INDEX(B2:H8,C10,C11)

It is compulsory that your selection does NOT include the headers.

INDEX returns the distance between cities

Leave a Reply

Your email address will not be published. Required fields are marked *