# INDEX Formula in Excel

Excel Index Formula is probably one of the most favorite Excel Formulae of many. In my previous post, I had mentioned about my favorite 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().

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.

Be Smart at Excel | New Skills for New JOB

A CHEATSHEET that Solve your EXCEL Problems
• eBook • Get Excel VBA Automation Course and Outlook Course as FREE • Lifetime Access

There are two forms of the INDEX function in Excel:

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 in Excel:

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.

The Ultimate EXCEL GUIDE LIBRARY
• Get Excel VBA Automation Course and Outlook Course as FREE • Lifetime Access • eBook

### 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)

A CHEATSHEET that Solve your EXCEL Problems
• eBook • Get Excel VBA Automation Course and Outlook Course as FREE • Lifetime Access

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)