We help IT Professionals succeed at work.

Index function in Excel

AXISHK asked
Last Modified: 2012-02-14

Read across this article about Index. Index culd returns either the value or reference to a value from a table or range

Why the first example return a value while the other example returns a reference ? Any idea ?


=Index(A1:D5,2,3)    return $3.5  -> a value
=Index(A1:D5,2,3)    return reference to a cell.
Watch Question

Most Valuable Expert 2011
Awarded 2010
This one is on us!
(Get your first solution completely free - no credit card required)
Most Valuable Expert 2012
Top Expert 2012

Actually, the example is showing two implementations of the INDEX function.

The first example is


where array is a contiguous range of rows/columns, row_number and column_number define an intersection.  PS - if you omit row or column number (but not both), you would get an array result.  E.g., Index(A1:D5,,3) would return the array reference in column 3, and SUM(INDEX(A1:D5,,3)) would return that sum.

The second example is quite interesting.  It is

=Index(reference,row_number,column_number, area_number)

In this instance, you can include reference, which is a comma-separated list of array ranges like this:


The next most important parameter to understand is the area_number.  In the example, above, using 1 as the area number would force the INDEX function to use A1:D6 for its array reference.  Using 2 as the area number would force the INDEX function to use A7:D12.  Of course, the reference parameters could be overlapping, as in the link you posted.

Attached, I've created two similar tables.  The first example of INDEX returns information about the first table.  The second example of INDEX returns information about the first or second table, because I built both tables in as the reference, and selected area number 1 for one example and area number 2 as the second example.

Now, why does the tip say it returns a REFERENCE to the CELL (re: marking the row/column intersection)?

Here's a little better tip on that: http://www.exceluser.com/explore/questions/debugindex.htm

That's because using the index function can indeed return a reference, just like MATCH can as well.  If you create a range name, and put your index formula there, check it so the formula is saved, THEN click on that formula in the name manager, you'll see that its referring to that cell reference (note the shifting-dotted-dashed square around the range/cell being referenced - like when you COPY something). (see below picture). So INDEX (both implementations, actually) can return not only a value but a reference (like reference to an array) that can be used to do things like, say,  dynamic ranges, support other array-like functions like SUM, etc.

It IS a reference!
You can use this example as well with OFFSET, MATCH, etc., and that's how you can test dynamic ranges, that your references are correct, as well.  So, when INDEX is referring to a range in a workbook, it not only returns a value, but a reference as well.  You just have to know how to use the reference when you need it.

Please see attached for demonstration.  Hope this clarifies!

Rory ArchibaldGrand Poobah
Most Valuable Expert 2011
Top Expert 2011

Personally, I disagree with that article (and the Help file). The two syntaxes quoted are identical and both return a reference but, as teylyn said, the visible result depends on how you use it.
The array syntax only applies if you pass an array (constant or calculated).
Most Valuable Expert 2012
Top Expert 2012

Agreed.  teylyn, very concise ;)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.