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.

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.

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(

3. Add the reference as:

=INDEX(A1:E10

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

4. Add the next argument as:

=INDEX(A1:E10, 7, 4)

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.

5. Press Enter.

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.

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.

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(

3. Select the array.

=INDEX(A1:C10,

4. Add the row and column numbers and close the brackets.

=INDEX(A1:C10, 8, 3)

5. Press Enter.

INDEX formula returns your result as:

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.

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(

3. Enter the reference containing the lookup value to be returned.

= INDEX(E1:E10

4. Combine it with the MATCH function.

= INDEX(E1:E10, MATCH( 

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.

5. Enter the lookup_value as:

= INDEX(E1:E10, MATCH(A6, 

6. Reference the lookup_array as:

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

7. Enter the match_type – we used 0 for the exact value.

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

8. Add the closing brackets.

The final formula looks like this:

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

9. Press Enter.

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

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.

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(

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

= MAX(INDEX(

4. Insert the reference as:

= MAX(INDEX(A1:C10,

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

5. Add the column number and close the brackets.

The final formula looks like this:

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

6. Press Enter

And INDEX returns the result as:

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 VLOOKUP, Use XLOOKUP in Excel, Use the INDEX MATCH Function, and more.

Frequently asked questions