How to Use the INDEX Function in Excel – With Examples (2024)

INDEX function belongs to the family of LOOKUP and is an awesome function at its base.

Its primary purpose is to return a cell reference from a specified array.

But the other LOOKUP functions do the same thing, no? So what distinguishes INDEX from other functions, and how do you use it? 🤔

Read on to find answers to these and a lot more questions. This guide has all you need to know.

If you want to practice the INDEX function in real time, download our sample workbook here.

How to use INDEX – reference style

The Excel INDEX function has two versions of its syntax. These are referred to as the array form and the reference form.

Reference style

Let’s first discuss the reference argument of the formula.

Its syntax is:

=INDEX(reference, =INDEX(reference, row_num, [columm_num], [area_num])

  • reference argument refers to the range or ranges you select – you can select more than one range. If you have multiple ranges, separate them using a comma like (A1:C4, D1:F4)
  • row_num parameter specifies the row number from where you want to extract the result.
  • column_num parameter specifies the column number containing the value to be extracted.
  • area_num is an optional argument. It is only used when you insert two or more ranges in the reference parameter. It specifies a particular range from the two.

Now that we are well-equipped with its syntax, let’s see how it works on actual data 🤓

We have the following example data set.

Example data for reference form

It contains the names of some students and their marks in three subjects. We want to find the total marks of Daniel B. given in cell E7.

So to do that:

  1. Select a cell.
  2. Enter the formula as:


INDEX formula
  1. Add the reference as:


Index functions - reference style

This specifies the range INDEX will look up for our value.

  1. Add the next argument as:

=INDEX(A1:E10, 7, 4)

Adding row and column

The two recent arguments specify the row and column numbers where the lookup value exists. INDEX will search the entire row and entire column for the value.

Since we only had one range, we didn’t use the area_num parameter.

  1. Press Enter.
INDEX reference style result.

The Excel INDEX function returns the value 59, which is exactly what we want.

This might seem petty but wait till you use the INDEX function with large data. Your mind will be blown away 🤯

How to use INDEX – matrix style

We’ve seen how the INDEX reference style works. Let’s now explore and learn more about its array version below.

Array constant of INDEX formula

The syntax of the array form is given as follows:

=INDEX(array, row_num, [column_num])

  • The parameter array refers to the range of cells where we want to find our lookup value.
  • The row-num argument is the row number in the array where the lookup value exists.
  • The column-num argument specifies the column containing the lookup value.

These arguments are similar to the ones used in the reference form. And the results are also pretty identical.

Let’s test the array form on a real data set.

We have the following sample data.

Example data set for array form

And we want to find the sales percentage of iPhone 11.

To do that:

  1. Select a cell.
  2. Enter the INDEX formulas as:


INDEX function
  1. Select the array.


INDEX formula array form
  1. Add the row and column numbers and close the brackets.

=INDEX(A1:C10, 8, 3)

Adding row and column numbers
  1. Press Enter.

INDEX formula returns your result as:

iNDEX function shows the result

How cool is that? 🎯

Other INDEX formula examples

Let’s see some examples of the INDEX formula with other functions 😃

INDEX formula example #1

We will combine the INDEX and MATCH functions – the most commonly used duo. So let’s get started.

We have the following data set.

Data set

It contains information about the employees of a company. It shows their departments, salaries, joining years, and leaves.

We need to check the number of leaves of Alex J. using the INDEX and MATCH Formula.

To do that:

  1. Select a cell.
  2. Enter the formula as:


INDEX formula
  1. Enter the reference containing the lookup value to be returned.

= INDEX(E1:E10

INDEX reference
  1. Combine it with the MATCH function.


INDEX and MATCH function

Now, we need to add the arguments of the MATCH function. The first argument is the reference against which it will find the lookup value.

  1. Enter the lookup_value as:

= INDEX(E1:E10, MATCH(A6, 

MATCH lookup value
  1. Reference the lookup_array as:

= INDEX(E1:E10, MATCH(A6, A1:A10, 

Lookup array value
  1. Enter the match_type – we used 0 for the exact value.

= INDEX(E1:E10, MATCH(A6, A1:A10, 0

Match type value
  1. Add the closing brackets.

The final formula looks like this:

= INDEX(E1:E10, MATCH(A6, A1:A10, 0))

 Closing brackets
  1. Press Enter.

And voila! The MATCH function returns the leaves number of Alex J. as:

Final value of INDEX MATCH function

You’ve done quite some work today 🥇

INDEX formula example #2

Let’s try an easy INDEX example for this one.

Say we have the following data set that shows the total sales of some T-shirts.

Example data

We will combine the INDEX function with the MAX function to find the highest sales made in this range.

To do that:

  1. Select a cell.
  2. Enter the formula as:

= MAX(

MAX function
  1. Now add the INDEX function as a parameter of the MAX function.


MAX INDEX function
  1. Insert the reference as:


Reference for formula

We will leave out the row-num argument because we want INDEX to find the highest value in the third column.

  1. Add the column number and close the brackets.

The final formula looks like this:

= MAX(INDEX(A1:C10, , 3))

Formula for MAX INDEX
  1. Press Enter

And INDEX returns the result as:

Result of MAX function

Pretty easy, no? 👀

This might seem a little uncalled for. But the INDEX function can be really resourceful when combined with other intricate functions for data crunching and analysis.

That’s it – What now?

Wow, we’ve learned a lot today 😅

We saw what the Excel INDEX function is and how it works. We also saw its two versions and some important INDEX examples with other functions.

It’s a really fantastic function – you just need to know when and where to use it. Luckily, Excel has a huge variety of functions similar to and more powerful than the INDEX function.

Some of these include the VLOOKUP, IF, and SUMIF functions, but there’s more to it.

You can learn these incredible functions and more for free in my 30-minute email course. It’s delivered right to your inbox only at the cost of your email address. So join now! 😃

Other resources

Did you enjoy reading this article? If you did, we have a lot more in store for you.

Read similar topics: How to use VLOOKUPUse XLOOKUP in ExcelUse the INDEX MATCH Function, and more.

Frequently asked questions

To know what INDEX does in Excel, you first need to know how it works. The INDEX function is an array formula. It lookups up a value in a range as we specify its row and column. INDEX returns the value given at the intersection of the specified row and column.