Working with Excel INDEX Formula

Excel Index is probably one of the most favourite Excel Formulae of many. In my previous post, I had mentioned about my favourite Excel formula. And it was not INDEX(). In my excel training sessions, when I happen to ask my participants about their favorite Excel formula, I am not surprised to hear the answer to be either VLOOKUP() formula or INDEX() or MATCH().

Learn Index formula in this Excel Training article:

The Excel Index formula returns a value or the reference to a value from within a table or range. When you wish to find a particular value from a range of cells, Index function in excel helps you get that value.

Index

There are two forms of the INDEX function:

  1. The array form
  2. The reference form.

The array form returns the value of a specified cell: INDEX(array, row_num, column_num)

The reference form returns a reference to specified cells: INDEX(reference, row_num, column_num, area_num)

In the above Index formula:

An array is a range of cells

reference is a reference to one or more cell ranges.

row_num is the number of the row from which to return a reference.

column_num is the number of the column from which to return a reference.

area_num selects the particular range in reference from which to return the intersection of row_num and column_num.

Also read and understand Excel VLookup Formula, with infographic and videos.

Index function applies to the following versions of Microsoft Excel: Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.

How to get the nth item in the list using excel index formula?

Index(A1:D5,n)

Now let’s see some examples using the data shown in the above image.

Examples for Excel Index array form based on above diagram:

1. Index(A1:D5,3,1)

Output: Swann,Trina

2. Index(A1:D5,5,3)

Output: M

Few examples of Excel Index reference form based on the above diagram:

1. Index(A1:D5,3,1)

Output: reference to cell A3 (which contains the value Swann,Trina )

2. Index((A1:B5,C1:D5),4,2,1)

Area number 1 gives the output from the first part of the reference specified, i.e. A1:B5

Output: B4 (which contains the value 9821181333)

3. Index((A1:B5,C1:D5),4,2,2)

The area number 2 gives the output from the second part of the reference specified, i.e. C1:D5

Output: D4 (which contains the value 21)

Do you use Excel INDEX() formula? Please share your tips and experience with INDEX() using comments.

I hope you found this article on Working with Excel INDEX Formula useful, Start Learning more with our FREE Online Excel Courses now.