The Imposing INDEX

| 257 Comments | 0 TrackBacks


From my perspective, the Excel INDEX function is the single most important in the roster of Microsoft Excel functions.

Now that might be surprising considering the function's humdrum name, but please pay close attention, because INDEX is one of the magical secrets of how to use Excel! So what's so great about the INDEX function? It's nonvolatile, sprightly, agile, and versatile. Excel INDEX can return one value or an array of values; it can return a reference to one cell or to a range of cells. INDEX works well on either side of the three Reference Operators - the colon, the space, and the comma.


index_function_parameters_excelhero.png

 

The idea with INDEX is that you give it a range (or an array) and then specify an element to return. 

So,

=INDEX(A1:A5,2)

...returns a reference to cell A2.

And,

=INDEX({95,96,97,98,99},5)

...returns the value of 99.



Note:  If you use an international version of Excel, some of the array constants may not work as presented.  Please see the instructions at the end of this article.



Nothing extraordinary thus far. But it is crucial to understand and so I'll reiterate that the first example above returns a REFERENCE, while the second returns a VALUE. 

Also, note that both of these examples demonstrate how the Excel INDEX function behaves when passed a vector. A vector is a one-dimensional array or range. When passed a vector, INDEX does not care whether that vector is vertical or horizontal. The second parameter of INDEX identifies itself as row number. But this is not correct, when a vector is passed in. Instead of being the row number, the second input becomes the element number for a vector - and as such, horizontal vectors work just fine with this notation. In the second example above, five is not the row number, it is the element number!


But we can force INDEX to behave in its normal two-dimensional fashion:


=INDEX({95,96,97,98,99},1,5)


...also returns the value of 99.


And it is with this two-dimensional behavior that INDEX begins to really shine. Obviously,

=INDEX(A1:C5,1,3)

...returns a reference to C1. But what is surprising to most users is that both of the following do as well:

{=INDEX(A1:C5,0,3)}
{=INDEX(A1:C5,,3)}



excel-hero-academy-web-banner1.jpg


Return Whole Rows or Columns

In fact, until the user understands what is happening here, they might think that the function has a bug because the overriding theme with INDEX is that unlike OFFSET for example, it must return an element from within the range or array passed to it - and row zero (or column zero) is outside. So what does ZERO or a null value mean in this setting?

If the row_num parameter is zero or missing, this directs INDEX to return the entire column specified by the column_num parameter!

{=INDEX(A1:C5,,2)}

...returns a reference to the range, B1:B5.

And the converse is also true - specifying a zero or missing column_num will return an entire row. The catch here is that the missing column_num MUST include the comma, like so:

{=INDEX(A1:C5,2,)}

...which returns a reference to the range, A2:C2.


But remember that when a particular cell is given a reference to a multi-cell range or that cell is set equal to an array, just the first element shows in the output cell - so most of the row or column returned by the two above INDEX formulas will be hidden. But you can array-enter one of these Microsoft Excel formulas over a range to see the entire output. You do this by selecting the output range first, typing the formula in the formula bar, and entering it by Control-Shift-Enter.

So yes the technique works with returning entire rows or columns of a two-dimensional array as well:

=SUM(INDEX({1,2,3,4,5;6,7,8,9,10;11,12,13,14,15},3,))

...results in 65, which is the summation of the entire third row of the input array.


Lookups

This ability to return entire columns or rows from a larger range or array is extraordinarily useful.  

Suppose we have a table or list of metrics for countries in the range of A1:M200. We can create a named formula that refers to this range - let's call it simply, d, for data. Then we can name individual columns of the data painlessly. If the country names are in the first column, we can create a named formula:

Country:   =INDEX(d,,1)

And if population is in the 4th column, we can create a named formula:

Population:   =INDEX(d,,4)

Now suppose we wanted to lookup the population of Scotland, all we need to do is:

=INDEX(Population,MATCH("Scotland",Country,0))

The Excel MATCH function simply produces a row number for INDEX.

This method of lookup is on par in terms of speed as doing a VLOOKUP, if you are doing just one lookup. But it has advantages over VLOOKUP. It is not restricted to looking up a column to the right like VLOOKUP is. Excel INDEX MATCH is significantly quicker than VLOOKUP if we are doing lookups for a list of countries, and we array-enter the one formula over the entire output column.

If we need to return multiple columns of metrics for a list of countries, the speed benefit is even greater. Suppose that GDP is the 2nd column of the data and that Capital was the 11th column.

If on a new worksheet, we wanted to output three columns of data for each country, Population, GDP, and Capital, the most efficient way to do so is to dedicate one column to create a common index, and then array-enter the INDEX formula over the entire three columns of output.

In this scenario, column B would be the list of countries. Column A would be dedicated to the common index. In A2, we would enter:

=MATCH(B2,Country,0)

...and then copy this formula down as far as the countries are listed in column B.

And then in columns, C, D, and E we would array-enter over the entire range that extends as far down as the countries:

{=INDEX(d,A2:A100,{4,2,11})}

To be clear, this means that if we had 99 countries in column B, we would select C2:E100, and then in the formula bar we would type the above formula, and then enter it by Control-Shift-Enter.

The end result is that the one formula is executed just once (in array fashion) and is brutally fast leaving a very light, non-volatile footprint in your model.


Sums

This usage of INDEX is a great way to power SUMPROPDUCT as well. For example:

=SUMPRODUCT( (Left(Country,1)="U")*Population )

...which returns the total population for all countries that start with the letter, U.

And while named formulas are self-documenting, this works just as well:

=SUMPRODUCT( (Left(INDEX(d,,1),1)="U")*INDEX(d,,4) )



Excel School Online Training Program



The Dynamic Range

Building on this idea, we can alter the named formula, d, so that it results in a dynamic range instead of a fixed range. And here INDEX reigns supreme. 

A Dynamic range is often constructed with OFFSET or INDIRECT. Unfortunately, both of these Microsoft Excel functions are volatile, which simply means that all formulas that include these functions will recalculate every single time anything on the worksheet changes. For example, adding a value to any blank cell will cause all volatile formulas (and any other formulas that depend on them) to recalculate. Depending on your model design and size, this can have a major impact on the fluidity and responsiveness of the model.

INDEX can be used to create a dynamic range, and not only is it nonvolatile, it is way faster than either OFFSET or INDIRECT. In fact, the improvement in performance is so great that INDEX should be the foundation of all dynamic ranges in professional models.

The approach to creating the dynamic range is different than the approach used for OFFSET or INDIRECT. For those functions, the range is created within the function. With INDEX on the other hand, the dynamic range is produced by using INDEX on one side (or sometimes both) of the Range Operator, which is the colon.

For example, consider this normal looking range reference:

=A2:A100

By itself, this reference could be a hard reference to the country names in our list of countries. Assuming no blanks in our list and that there is a column header in A1, perhaps "Country", the following formula would return the name of the last country in the list:

=INDEX(A:A,COUNTA(A:A))

But the INDEX function here is really returning the reference to the last cell in column A with a country name, and then the equal sign forces the value of that cell to be returned. In the scenario that we have painted, this INDEX formula is really returning the reference, A100.

So the following two formulas point to the exact same range:

=A2:A100
=A2:INDEX(A:A,COUNTA(A:A))

But there is a significant difference. The first is a hard coded, static reference. The second is a nonvolatile, dynamic range that will expand or contract as the number of countries in the list changes. Please note that before we replace the Refers To value of our named formula, Country, with this INDEX based dynamic range, we need to make the references absolute. It would then look like this:

Country:   =$A$2:INDEX($A:$A,COUNTA($A:$A))

We can use the same techniques to create a two-dimensional dynamic range so that the number of columns is also dynamic:

d:   =$A$2:INDEX($1:$65535,COUNTA($A:$A),COUNTA($1:$1))

And with d defined in this dynamic manner, we can still do all of the wonderful row and column referencing from above, such as:

{=INDEX(d,$A2,{4,2,11})}


As a footnote to this section on dynamic ranges, I want to point out that when you use a function on either side (or both sides) of any of the three Reference Operators, the resulting formula always recalculates when the workbook is opened. So while INDEX is nonvolatile, it becomes what I term quasi-volatile when used for dynamic ranges - but this is orders of magnitude better than volatile, and so it remains the best dynamic range foundation by far.

And as a post script to this section on the dynamic range, you may very well ask should I not just use the new Structured Table References available since Excel 2007? While STR is a robust option, it is heavy. If you need speed, nothing beats the alacrity of INDEX.



Noncontiguous Areas

When working with ranges, INDEX offers a fourth parameter to select the AREA to work with from a noncontiguous input range. The areas are referenced by integer in the order that they appear in the input range. For example,

=INDEX((data1,data2,data3),,,2)

...returns a reference to data2. And

{=INDEX((data1,data2,data3),,1,3)}

...returns the entire first column of data3. With a little ingenuity, this can be extraordinarily useful for charting... and many other activities. And you should note that the number of areas specified has no hard limit, and those noncontiguous areas need not be of the same size!


More

All of this just scratches the surface of what is possible with this essential member of the Microsoft Excel functions. It can be used in countless situations, even to compare Excel worksheets, etc.

Some of the synonyms for the word, "imposing", in my thesaurus are impressive, august, commanding, effective, exciting, magnificent, and mind-blowing. The INDEX worksheet function is truly imposing!



International Versions of Excel

Throughout this article, I have used array constants within formulas.  This is a powerful technique.  In the English Language version of Excel, the symbol used to separate the columns in an array constant is the comma.  The symbol used to separate rows is the semicolon.

Other language versions of Excel use different symbols for column and row separation.  However it is easy to discover what symbols are used in your version of Excel.

Open the VBEditor (ALT-F11), and place the cursor in the Immediate Pane (CTRL-G).

Enter the following two lines in the the Immediate Pane (it's OK to use the clipboard):


?Application.International(xlColumnSeparator)

?Application.International(xlRowSeparator)


Click on each in turn and press ENTER on the keyboard.

Uses the printed characters to separate columns and rows in array constants.





And here are some other reference articles at Excel Hero:

The Imposing INDEX
The Venerable SUMPRODUCT
- I Heart IF
5 And 3 Is 1
Live Calendar Musings
Which Function To Use
Excel VBA Shortcut Range References
Excel Partial Match Database Lookup
Excel High Precision Engineering Chart #1
Excel Zip Code Lookup
Excel Dynamic Checkmark
Animated Business Chart
Excel Acrobat PDF Form Filler


Learn Advanced Excel!

Advanced and Creative Formulas is just one of literally dozens of advanced methods that my students in the inaugural class of the Excel Hero Academy are learning. 

I've received dozens of messages from students telling me that the academy is the best Excel training they have ever found at any price and that it is possibly the best investment they've ever made. Wow.

In a few weeks I will be offering the course again. There's already over 250 people on the interest list for the next class. Why not join us? Just fill the form below and I'll send you more information as we get closer!



 

If you liked this article, please share it!



Your Ad Here

No TrackBacks

TrackBack URL: http://www.excelhero.com/cgi-bin/mt/mt-tb.cgi/101

257 Comments

Daniel
Another great article
Hui...

Daniel,

Excellent post.
With this explanation and the videos from the Excel Hero Academy, I finally understood and grasped the full power of the INDEX formula.

I used the index formula before but only for lookup formulas such as the Index - match - match

Jose Carranza
Excel Hero Academy Student!

Worth noting that =INDEX((data1,data2,data3),,,2) is equivalent to =CHOOSE(2,data1,data2,data3) as the CHOOSE function also accepts ranges.

In fact the CHOOSE function can be better at returning whole rows/columns, because it doesn't care if your data ranges are on different sheets or even in the same workbook. Whereas try to point index to multiple ranges on different sheets and you will get an error.

Not sure what the speed difference would be, though.

Jeff Weir
EHA student and occaisional homework completer.

Nice article! Especially with some slightly different presentation as compared to the Excel Hero Academy :)

Jim
EHA Student

If D = A1:E10 and If only MS would tweak Index to work as = Index(D,,{1,3}) to return a discontinuous range A1:A10,C1:C10 then it would be a truly awesome function.

But I ask too much from a company focused on providing more shades of blue rather than make "real" improvements in their software.

Imposing article. Thanks - it was a good read!

Ulrik

Excellent article
Did not realise that {=INDEX(A1:C5,,2)} was returning the entire row
Need to go back to those EHA videos
Thanks
Bruce

Small question:
If cells B7:B11 have values 95,96,97,98,99 and
if cells c7:c12 have values 100, 101, 102, 103, 104,105
and if you create a formula:
=INDEX(B7:B8:B7:C10:B12,6,2) - not that I ever would, I was just playing around;
why is the resulting value returned 105? 6,2 seems to point to C12 but this appears to be outside of the formula range-when double clicking the cell with the formula. I thought index only worked on data in the range described. Does it have something to do with my weird range description and the use of so many colons?

Thank you
Terry
slow EHA student

Jeff,
I had not realized that the areas have to be on the same sheet....seems like a major bug.

a great article. thank you. the EHA course is great. this extension article again proves the value being delivered in each lesson.

@Daniel-a question... the MATCH function seems to only like being given a one column (or row) for the lookup_array. is this correct? you would think that you could give it a multi column or row array and then feed it an arguement as to which single column to use for the match. this would seem a lot cleaner from a formula standpoint.

-bill, EHA student.

Terry
The : operator is making the smallest rectangular area encompassing all your cells
Index then goes to the 6th row 2nd column and gets 105 as it should

=INDEX(B7:B8:B7:C10:B12,7,2) will give an error as the combined range is only 6 rows high

Daniel discussed the : operator at the start of: http://academy.excelhero.com/videos/module06/mod6_reference_operators/

Is this a typo?


In this scenario, column B would be the list of countries. Column A would be dedicated to the common index. In B2, we would enter:
=MATCH(B2,Country,0)

Shouldn't the formula be in cell A2 instead of B2?

This is the best article about the INDEX function ever written. In fact, it's the best Excel article I've ever read. Period.

Thanks for putting this together. It's AWESOME.

Another good article to help explain the dark mysteries of the Excel internals.

However, I found the lookup section to be confusing. First I had to realize that you were creating the lookup results on a new sheet separate from the data table.
2nd, I could not get the array entered formula ‘=INDEX(d,$A2,{4,2,11})’ over the range C,D,E rows 2-100 to work. It just returned the values for the first country listed. If I array entered it over just the first row and three columns and then copied it to the other rows it worked fine. I'm probably missing a step.

Greg K - EHA student

And to think I used to avoid 'Index' unless I was really desperate. Nice job making an imposing function approachable.

Glenn - EHA student

Great article. I had no idea that INDEX could return whole ranges, or that you could index multiple areas.
Darin M - EHA student

Yes indeed. Very nice.
Sums up a lot of the fragments on EHA, for me.
Got lost a bit with the SumProduct but only because I have never used it, save EHA introduction. All the rest very clear and useful.
Thanks,
Martin, Novice EHA student.

Daniel,
Thank you for this great article!
I have a question about the lookup formula: ‘=INDEX(d,$A2,{4,2,11})’ which is linked to GregK's post above. In order to use the same array formula for the entire C2:E100 range, shouldn't the formula rather be: ‘=INDEX(d,$A2:$A100,{4,2,11})’? Please let me know whether I am missing something. Thank you.

LVG and GregK and Reauvain,

Thanks for pointing out the typos. All fixed now.

Regards,
Daniel Ferry

Wow, excelsemipro!

Thank you very much indeed.

Thanks to all for the great feedback!

Regards,
Daniel Ferry
Excel Hero Academy

Jeff W,

I use CHOOSE all the time for that very thing. But the truth of it is that INDEX is far faster. And if you need to drill into the Area returned, INDEX does it in one step with one function call, whereas with CHOOSE you would need at least two, one for the CHOOSE and one for the drilling.

sam and Jeff,

I think the limitation is related to the fact that all references produced by any of the three Reference Operators must work on operand references on the same sheet.

This is why:

=SUM((Sheet1!B2,Sheet2!B2))

produces and error, while:

=SUM(Sheet1!B2,Sheet2!B2)

does not.

Like SUM, CHOOSE has multiple separate reference inputs (254 of them for CHOOSE, 255 for SUM).

The first formula above uses the UNION operator in an attempt to combine the ranges, which fails. But since INDEX has only one input for a range, you must use this method - so one sheet only (just my theory).

Daniel,
Thanks for this explanation..It is indeed the limitation of the comma and space operator
I tried define Combo = Sheet1!B2,Sheet2!B2 and then sum(Combo)on another sheet and it failed

What is difficult to understand is what kind of a range does this 3d reference Sheet1:Sheet2!A1:A4 actually return....does it return a single range or a discontinuous range..
Sum(Sheet1:Sheet2!A1:A4) works fine but
Index(Sheet1:Sheet2!A1:A4,1,1,1) gives an error

sam

@Greg, Read my comment above regarding the inability of index to accept an array for Row or column num and return an array of discontinuous cells

sam

@Dan,
In most instances Index would need to be combined with Match to replicate a Vlookup....So I think your next article should be "The Magical Match..."

Great article & some very useful applications for this overlooked formula. I have a question, you say:

"The idea with INDEX is that you give it a range (or an array) and then specify an element to return.
So,
=INDEX(A1:A5,2)...returns a reference to cell A2.
And,
=INDEX({95,96,97,98,99},5)...returns the value of 99.
Nothing extraordinary thus far. But it is crucial to understand and so I'll reiterate that the first example above returns a REFERENCE, while the second returns a VALUE"

How does excel know when to return a reference as opposed to a value? Has it got something to do with the way it is used, e.g. if it is used on one side of a range operator it will always return a reference??

Can anybody help a budding excel hero?

:)

Prem
EHA

Great article, i've always used OFFSET in the past and am now in the process of converting my projects over to INDEX.

Daniel,

Great Article as usual.

I will not be able to live without INDEX from now on. Thanks

Nacho
EHA Student

This article and your Venerable SUMPRODUCT articles should be required reading for anyone using advanced Excel formulas.

Since learning the details of how to use INDEX and SUMPRODUCT in the academy, my spreadsheets perform so much better that it makes me wonder how I ever got by without them. The best part is the users feel the same way.

Hi, Prem.

It's really as simple as this: When INDEX is working on an array it returns values; when working on a range, it returns a reference.

Some arrays may not be obvious to the uninitiated. I have made them array constants for this article, but they could be arrays created by function, for example ROW(1:10)... or any other method that returns an array. If INDEX is processing an array it returns a value from that array - WHICH BY DEFINITION HAS NO REFERENCE ADDRESS.

When working on a range, the INDEX function ALWAYS returns a reference. You can test this on a worksheet by using:

=ISREF(A1)

...assuming that A1 contains the result of an INDEX based formula. Try passing that INDEX formula a range... then try passing it an array.

ahhhhh! Now I get it. Using the ISREF test has made it much clearer for me now. Thanks Daniel :)

Another great article - I learned a couple of new useful things about INDEX.

EHA Homework

@Prem,

I have another interesting use of index. I do a lot of budgeting work and need to allow sum of values for any start month to any end month. When we say Index returns a reference it can be used as a starting point/ending point of an area.

e.g. Your data is as follows:
Cells A1:A12 contains Jan, Feb, Mar... Dec. (having Named range Months)
Cells B1:B12 contains 10,20,30,40...120. (having Named range Values.)

You can give a formula like
=SUM(INDEX(Values,3):INDEX(Values,7))
This gives a value of 250 which is total of months for Mar to Jul.

Moreover just link 3 and 7 in above formulas to cells connected to drop down box and you allow users creating dynamic totals in no time.

The same technique can be used to avoid indirect/offset and can be used for dynamic named ranges which can be used in creating dynamic charts.

e.g. A named range xMonths can be created by just giving in the refersto. =INDEX(Months,3):INDEX(Months,7)

Hope this helps to understand diff between returning reference and returning value and how returning reference can be used in different scenarios.

BTW another great article, Daniel.. hoping to join the Spring excel hero academy..

Something screwy with INDEX:
This:
=INDEX({1,2,3,4,5;6,7,8,9,10;11,12,13,14,15},1,)
returns {1,2,3,4,5} as you would expect.

And this:
=INDEX({1,2,3,4,5;6,7,8,9,10;11,12,13,14,15},3,) returns {11,12,13,14,15} as you would expect.

What would you expect this to return?:
=INDEX({1,2,3,4,5;6,7,8,9,10;11,12,13,14,15},{1,3},)

I was hoping for the first and the third rows, i.e. {1,2,3,4,5;6,7,8,9,10}

But I got two different things depending on how I check the output:
1. if you go to the formula bar and press F9 it gives you {1,2,3,4,5}.
2. When you enter it into an array of cells, it spreads these elements over 2 rows:
{1,11,#N/A,#N/A,#N/A;1,11,#N/A,#N/A,#N/A}

huh?

And when you replace the {1,3} with {1;3} (i.e. semicolon instead of comma, so that you have =INDEX({1,2,3,4,5;6,7,8,9,10;11,12,13,14,15},{1,3},) then:
1. if you go to the formula bar and press F9 it still gives you {1,2,3,4,5}.

2. When you enter it into an array of cells, it puts the number 1 in each cell in the first row, and the number 11 in each cell in the 2nd row.
What?

@Jeff Weir,

You are selecting too many cells and omitting the column argument (which I think is hiding how this works from you). See if these two examples help you any.

1) Select three contiguous cells across a row and array enter this formula...

=INDEX({1,2,3,4,5;6,7,8,9,10;11,12,13,14,15},{1,3,2},{2,5,3})

2) Now select three contiguous cells down a column and array enter this formula...

=INDEX({1,2,3,4,5;6,7,8,9,10;11,12,13,14,15},{1;3;2},{2;5;3})

Note the group and position within the group for each number returned as it relates to the positionally paired row/column numbers specified. In effect, you are returning multiple numbers from a table of values.

@Rick: ahh...this is like the kid's grid game battleship! You just sunk my destroyer :-)

Thinking about this more, I'm guessing that when you only are only trying to return a single row like this:
=INDEX({1,2,3,4,5;6,7,8,9,10;11,12,13,14,15},1,)
then when you leave the column argument blank, then excel DOES NOT default that argument to one...instead it is programmed to return you the entire row i.e.
But when you want to return a noncontinuous array of say rows 1 and three like this:
=INDEX({1,2,3,4,5;6,7,8,9,10;11,12,13,14,15},{1,3},)
, and you leave the column argument blank, excel defaults that omitted argument to '1' .

And as Rick points out above, if you supply two arrays as the argument for the columns and rows you want returned, it gives you the values at the x,y intersections of those pairs.

Jeff,

Almost.

For INDEX, leaving the row_num or the column_num parameter blank is the same as indicating a ZERO value (not a 1).

These are equivalent in every respect:

=SUM(INDEX({1,2,3,4,5;6,7,8,9,10;11,12,13,14,15},1,0))

=SUM(INDEX({1,2,3,4,5;6,7,8,9,10;11,12,13,14,15},1,))

@Rick again...do you think its inconsistent
that excel doesn't return the multiple numbers as an array that can be used in another function within the same formula? That is, array enter =INDEX({1,2,3,4,5;6,7,8,9,10;11,12,13,14,15},{1;3;2},{2;5;3}) over three cells in a column or row, and you get 2, 15, and 8 entered into those three cells. But click on the formula bar, push f9,and you only get one number.

Great post of really advanced uses of INDEX. It's also my preferred way to lookup stuff in Excel. I did a post comparing the various ways to do lookups and came the conclusion INDEX and MATCH is the best method. It's bit controversial though - like trying to convince someone that 'red' is the best color. Check it out here: http://www.sumwise.com/excel-lookup-formulas/

Hi,

Very nice article.

This is what I am trying. It does not seem to work. Can you please help?

1. I have a data entry form using form controls (not activex controls).
2. The Customer Project DB is a different sheet and has several columns. One of those is PID (Column B).
3. In the data entry form I use combo box for entering PID.
4. Since the list of PID is dynamic, I am trying to use dynamic range as suggested in this article.
5. So what I do is: In the controls tab of Combo Box format control, in Input Range I enter - $B$2:INDEX($B:$B, COUNTA($B:$B))
6. Since it is expected to create the dynamic range, it should give me a drop down of all the PIDs.
7. However, I get empty drop down.

Problem 2: How do I use this if the list is on a different sheet?

regards,

Jagmohan

Hi, Jagmohan.

You are very close.

All you need to do now is take that formula and use it as the "Refers To" in the Name Manager, which is CTRL-F3 from a worksheet.

In that dialog, create a name for your formula. You should then be able to use the newly created name as the reference for your control and this also works from another sheet.

Regards,

Daniel Fery

Hi,

Thanks Daniel.

I did that and it worked. I think it works this way because it returns a reference. Is that right?

When you get a reference to a cell, how to use it to assign a value to this cell? Only through VBA?

regards,

Jagmohan

Thanks Daniel,

I always thought that INDEX was just a function that went along with MATCH. I also never realized that INDIRECT and OFFSET were among the culprits of my dashboards slower than average performance. I'm keeping this article for future reference!

Best regards,
Jay
EHA 2 Student!

Daniel,

I didn't know you can Ctrl-Shift-Enter a formula over a range to get the resulting array values. I also didn't know the hit OFFSET gives. I have a rather large worksheet that uses INDEX and OFFSET. I will have to review it.

Thanks
John
EHA2 Student

Daniel,

Having used excel for years without ever touching the index function, I now don't know what I would do without it. (I do, but it would make life way more complicated) This sheds even more light onto the functions capabilities. I am definitely going to be using way more often now.

Thank,

Gene
EHA2 Student

Hi Daniel,
Thank you for the great explanation of dynamic ranges. I have long struggled with the concept, but your commentary sheds light on the great power it wields in the INDEX function.

I am very much looking forward gaining exposure to the subject matter of this course. It goes without saying, but you are a great instructor!

Bryan
EHA2 Student

Hi Daniel,
Thank you for the great explanation of dynamic ranges. I have long struggled with the concept, but your commentary sheds light on the great power it wields in the INDEX function.

I am very much looking forward gaining exposure to the subject matter of this course. It goes without saying, but you are a great instructor!

Bryan
EHA2 Student

Thanks nice post.

Great breakdown of a formula that I use but do not leverage. This is great insight of how to squeeze even more out of Excel.. Thanks for creating and recommending this article Daniel.

Calvin
EHA2 Student

I do use offset quite a bit and while I was aware of INDEX, never quite felt the need to explore it further. This article has provided some great reasons to explore it further.Awesome!

Used dynamic ranges and naming to make a pseudo-db style naming for complex formulas to improve accuracy and readability.

Really interesting stuff. Took a while to soak all of the info in. You mention how useful index can be for Noncontiguous Areas with regards to charting. I would like to see an example or better understand what you mean by that. I also think it would be wise for me to read your Sumproduct article. That is a formula I have not used in the past. Thanks for the great article.

Hoffy
EHA 2 Student

This is fascinating info, I have used INDEX and MATCH in the past but with no real understanding of what I was doing. I have already tried out a few experiments and I'm really happy with the results.

Abraham
EHA2 Student.

Outstanding article. This seems like a significantly better way to make dynamic ranges and link to cells!

Bob N.
EHA2 Student

Daniel,

Was there once a spreadsheet for download available for this blog post? It would help me better understand the article. I look forward to more.

Thank you,

Daniel,

I've used Index before, but never understood it's full capabilities. I'll be digesting this for a while to come, as I ponder its depths.

Geoff B
EHA2 Student

This article is fantastic. I've used elements of your post in the past -- sometimes with full awareness of what I was doing and other times just reverse engineering functions/formulas to get what I need without fully understanding. I know I'll be revisiting this article many times throughout the course and as I work on a few models presently for some clients. Really great stuff. Thanks, Daniel.

Analyst44
EHA2 Student

I was using Index in the past - but a bit "blindly" it seems - without fully understanding its potential - now it is a bit more clear (my third re-read of the article :)). Many thanks and keep up providing us with such details!

Marcin "Kuty" Markiewicz
EHA2 Student

I too have infrequently used the Index/Match combo but in no way have any clear understanding of the power available. The challenge to me is shifting my logic based on a new way of thinking. I feel like I am having to relearn how to drive all over again.

Charles McDermott
EHA2 Student

Please note that analyst44 and papercut_121 are the same person. I thought my username for EHA was analyst44 but it's the latter.

Tx.

Thanks Daniel for this great article. I was trying to avoid volatile functions like the plague and I have changed religion for dynamic ranges from offset/match to Index. I am struggling at the beginning with 2D dynamic ranges but will cope. Great increase in recalc speed in my large spreadsheets.

Regards,

Anil.

Wow, I read through this late last night and I think I'm going to need to make some examples to play with in a spreadsheet before it really soaks in.
Reading lists of arrays in an article just isn't the same as seeing the results in cells.
Looks powerful,
Thanks

OMfG! I wish I knew about the non-volatile dynamic range ages ago. I have some models that, over time, became soo slow. I mean, they worked and then at some point I hit a threshold and they became sooooo slow. They are super awesome models but are pretty much worthless when they get that slow, and it's because I used a lot of offset and indirect because of the need for dynamic ranges.

Yay for Excel Hero!

Daniel:

Amazing. I have been using INDEX and MATCH for a few years now, but have never even thought about the way you have incorporated the range names (I mean formula names!) here. This is just fantastic stuff.

Thanks!

Daniel

Really impressed with the article. The Sumproduct example incorporating text functions like LEFT will be very useful.

I'll need to re-read a few times to absorb all the information. Very impressed.

Martin
EHA student

This sounds so familiar with what our data warehouse says: use a/the index as much as possible and all your queries will run much faster. (especially in lookups, joins, counting)

I never realized this is applicable to Excel in the same way...

I have to admit that although using Excel for years I have never moved beyond using INDEX for anything beyond lookups to the left (Index-Match combo). I have begun using it in my work this week in conjunction with NAMED FORMULAS.

I have been using Tables a great deal since switching to Excel 2007, and these have the benefits of dynamic ranges. Could someone please advise me at what size table the speed difference really becomes apparent and compels a switch from Tables to dynamic ranges with INDEX?

Thanks,
John Noble
EHA2

My brain hurts!

I have used Index successfully before, but had no idea what I was doing, it just worked; I guess I'll be re-reading this article many many times before I know what I'm doing!

EHA2

I will never brag about Excel again.
I will never brag about Excel again.
I will never ......

This is SO cool. I have been looking for a course to challenge me. Clearly I have found it.
Now I just hope I'm up to the task!

Wow. Good stuff!
Bill Wilson
EHA2

Daniel

This article was most illuminating - I never imagined that INDEX is such a mighty function. I will keep on referring back to this article in the future. I sent you a spreadsheet in which I tried to to use all the formulas mentioned in this article will appreciate your feedback.

Haider

This is going to take a couple more reads to sink in, but I can see that I haven't been using INDEX to its full potential. I'm sure we'll be getting practice in the homework.

--Greg
EHA2

Wow

I had recently begun using the index and match functions to return values for salaries based on step and ranges and had no idea of the other uses.

I have tried with mixed success to incorporate the index feature into some of my existing work.

I see the potential and am looking forward to the day when index appears consistently in the recently used functions.

Roy

When you originally posted this article I envisioned the power and thought of numerous ways to implement it, but was unable to get it to work. Tonight I was able to get results instead of "#VALUE! ". Thank you. -- ajp EHA2 Student

This article helps begin to roll back to fog of the Index function. I'm starting to get it. I've been working with dynamic (or potentially dynamic ranges) at work, and knowing how to use this function and/or the new structured tables in E2007 will help. With some practice, and re-reading this article a dozen more times, I'll be an Index Hero!

Gary

Two words. Mind blown.

This has really opened my eyes to the possibilities. I had to read it twice (maybe a third time later) to grasp the concepts.

Great article - I would love some sample data sets to play around with and to test my grasp of the function. This page will be bookmarked for sure...

Scienceguy, EHA2

The INDEX function was a real turning point for me. The Microsoft article "How to look up a value in a list and return multiple corresponding values" introduced me to this and once you can understand, adapt and then master the fundamentals of formula below, great things will happen

=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX

I have read, reviewed and re-read the article. Still trying to digest it all. Lots to take in for a novice?
Still working on it.
Thanks

Can't wait to start experimenting with non-contiguous areas... thanks for illuminating the power of the humble Index.

Great Article!

I use INDEX and MATCH regularly to retrieve data items from datasets but this article shows the real power of the INDEX function. I can’t wait to implement some of the concepts in my own work.

Chris
EHA2

Present

Just an awesome article.
Till I read it I was not aware how powerful the INDEX function is.
I really enjoyed the Dynamic Range usage.

AAK.-
EHA2

I've also read and reread the article and I'm sure I'll come back a couple of times!
Difficult, but very interesting stuff!

Lorette
EHA2

Well - I know what I need to practice!

Thanks for the very informative and thought provoking article.

John (EHA2)

great article. i have been getting lots of practice with index for my work at the office. this has saved me hours of work already.

Tim (EHA2)

I've read this article several times, and now once more after signing up for EHA. Truly inspirational and the next step for me is to implement these techniques in the workplace with my colleagues.
- Juanito EHA2

This article is all new ground for me, and very powerful stuff it is! I look forward to much faster and simpler spreadsheets, but I'll need to further digest the concepts of arrays, and values versus references.

Phil (EHA2)

Read it.

wow, i've been trying to do things with vlookup where index would be much better solution. thanks!

Powerful function indeed! Thanks!

Very nice. I'll have to switch from vlookup to Index when capturing large blocks of data!

I use OFFSET often and thought I knew THE way to pick up data. I was not aware of function volatility and assumed that spreadsheet calculation must slow down when more complicated formulas are used. This article is really an eye opener. Will definitely practise the use of INDEX and named formulas at every opportunity.

Johnny Tam, EHA2

Awesome article! I will definitely have to re-think the way I've been doing things. Can't wait to try this.

Bonnie, EHA2

After going through this its easy to see what I have been missing out on over the years by ignoring this function.

Ryan, EHA2

I need to take the time to digest.

Letting it all sink in, it reminds me of Pivot Tables, which I use ALL the time.

Daniel,

Thanks for this brilliant post! Looking forward to understanding it more in the EHA class!

PwCSydney, EHA#2

I have been using Index/Match a lot since EHA1 however I haven't used it with array formula's yet so this is something I would like to work on. Interesting to read about using index with non-continguous ranges as well.

This was a very insightful article.

I now know the versatility and usefulness of the Index Function. I use OFFSET and INDIRECT a lot before, but now I'll try substituting it with the INDEX function. Thanks for this article.

Tai Du Luong

Hi Daniel!

This article, as your EHA, brings a very new and powerful perspective from using excel.

Index was the kind of formula that I had been used very few times until now, and not clearly understanding its benefits, what changed completely after reading your article.

Even so, I do know I still have to learn much more about this function, so I can really upgrade my excel models.

Thanks,

Rodrigo.

Hi Daniel,

Great article, going to try some of the things out that I read!

-Kevin Yu

Question:
In the Lookup Section where it says: " we can create a named formula: Population: =INDEX(d,,4)"

Does this mean we are creating this in a NEW column? We are not naming the existing column with the index formula? (I have been going mad over trying to figure that out)

If that is this case, arent we creating extra work by recreating the columns except making it index? Or this is just standard practice by ensuring we are not manipulating the underlying data?

-adam flath EH homework.

I work at Seek - Wow, this is a great discovery of yours, I would say. well, thank you for sharing this information. I actually use Excel and its stored functions only. I never thought you could come up with such kind of idea that makes everything easier.

It's all here and I have been thinking about it for a month. Some things have to be stared at for a long time, picked at, turned around, played with, left alone, come back to, tried on, failed. This is one of the best articles I have read on spreadsheeting. Daniel,Thanks for writing it and then sharing it with us.

This is going to be one of those articles that I keep on hand and constantly refer back to till I fully understand the intricacies.

Great article. I'm still confused a little bit about the Dynamic Ranges as I'm not sure why A1:INDEX(... doesn't work on my computer.

Hi,

Please confirm that Challenge #1 is the one we did before Module 1 - where we calculated the commission rates on variable sales levels.

Regarding INDEX, I can certainly see the power, but I have a fundamental misunderstanding of how it works, starting here:

"And it is with this two-dimensional behavior that INDEX begins to really shine. Obviously,

=INDEX(A1:C5,1,3)...returns a reference to C1. But what is surprising to most users is that both of the following do as well:

{=INDEX(A1:C5,0,3)}{=INDEX(A1:C5,,3)}"
What makes that first reference to C1 occur? is it because the INDEX function looks first at the Row then at the Column so comes up with Row 1 for the "1" and C for the "3"?
By the time I arrive here at the below, I am lost completely.
Thanks
Michelle

"Both of the following do as well:

{=INDEX(A1:C5,0,3)}{=INDEX(A1:C5,,3)}"

OK, rookie trying to keep up, here is my scenario -

so column a and b hold repeating numbers and values and i am only interested in the value in column c when column a and and b match the combined value the same value in a separate sheet.

for example:

Sheet1:
A1: AD123 B1: Blue C1: 30
A2: AD124 B2: Gre C2: 30
A3: AD125 B3: Red C3: 30
A4: AD125 B4: Blue C4: 29
A5: AD127 B5: Blue C5: 15

Sheet2:
A1: AD125 B1: Blue - [value needed]

Need to know what the value would is in column c as a result of the preceding values [A1&B1] in the above scenario the value I'm after is 15

hope this makes sense.

Daniel,

Excellent post.
Chris Lattanzio

Excel Hero Academy Student!

Interesting for someone from a SQL background.

Rocketman EH3

This was great Daniel. I used Offset for my dynamic named range and being such a large dataset it took 20 seconds every time I changed a cell contents to calculate now using the Index function of only one named range in this file it cuts down the calculation time to 0 when changing a cell not dependent on the named range formula and less then a second for ones that does use the named range. Awesome. I will already be saving time thanks to this.

Thanks Daniel.

I've been using INDEX for years but mainly just to "pluck values" out of a table, often combined with MATCH (when VLOOKUP isn't appropriate). However, two key jewels in this really great article opened up my eyes to so many other possibilities:

1) INDEX returns a reference
2) That reference can be much more than just a reference to a single cell.

I also appreciated the points about the merits of INDEX over OFFSET for "dynamic ranges" due to INDEXes non-volatility. I realize now why some of my larger spreadsheets have gotten really boggy.

Thanks for a great read.

INDEX is all new to me. I will have to read this a few more times, but I can see the power. I use VLOOKUP a lot and speed is an issue. I will start by converting some of my past sheets to this method.

o_0 SANTA MARIA!
This is actually the article that encouraged me to sign up for your school (EHA3).

I've been using the techniques for a few months now but re-reading this was a great assignment one. I hope the rest of the course is just as good.

Very insightful article. I think some of the sections could use visual examples. I.E. Lookup

Great Article Daniel, I did however notice a couple things.

1.=$A$2:INDEX($A:$A,COUNTA($A:$A)) will only provide the correct dynamic range if A1 is not blank. I have found that if A1 is blank then the range is short by 1 cell. I had to + 1 after COUNTA.

2.I also found that the non contiguous area formula {=INDEX((data1,data2,data3),,1,3)} worked well for ranges in one worksheet, however I received a value error when the data was in multiple sheets. I did not attempt multiple workbooks.

I am very excited to apply index and sumproducts to my worksheets where I have opted for other methods.

Very insightful article. I think some of the sections could use visual examples. I.E. Lookups - not entirely sure about it.

Interesting article. The part about dynamic ranges seems very useful, and I think I'm only grasping just a little bit of the power of Index.

EHA3 - ndarmyserver

Got Data from the internet to work with made it much more clear. couln't get the column 4 to display in the lookup example but got the column 1.

I have read this one many times and have been trying to implement it. This is the post that made me want to take the academy.
KenU; EHA3

faantastic ... can use this in an application at work instead of vlookup! already gained lots to help @ work. Thanks ... :-)

I use LOOKUP function daily. I am looking forward to using the INDEX function on a daily basis as well. Especially to test the claim that the INDEX function is "significantly quicker than VLOOKUP..." Need to study how I can used the INDEX function in my caculation models. And sumproducts too.

mbedan (EHA3)

OVERLOAD.....
When I first saw the information about this course from the Excel Addict email, I said this is the kind of course I need. I have worked with Excel for a few years, fumbiling around in it, seeking help online when I need to do something that I didn't know how to do.
Watching this modules lectures, and reading this blog, things are begining to make sense. Not completely (yet), but they are getting there. I will need to read this article again as well as watch the lectures again to gain a firm grasp of the Index function. I wish I had taken this course a few years back before I did a program to "run" an auction. It would have made the program and auction go much better! As it was my program did very good. Daniel keep up the good articles there is a ton of Gold in them!

I will definitely have to re-read this and play around with it to fully grasp how it works. I've used index and match, but that was only recent. Before that it was the VLOOKUP.

The ability to embed this function within itself and to use or not use some of the functions augments is impressive. Definitely need to play around with it in order to fully understand its potential

Daniel,
Great article. Can we review INDEX in your class? I have not used that functionality. Thank you.

This is a whole new perspective on a neglected function. Comprehensively covered, too. I'm going to review quite a bit of what I've been doing for many years as much of it will work better using INDEX functions. . .

Peter H (EHA2 & EHA3)

I've used excel for about 4 years now. I have only begun using Index/Match in the last 6 months. This was a real eye opener and a struggle to get my head around. I can definitely begin to use INDEX in some of my models as I have used the LOOKUP function exclusively. I would love to have more "problems" to work through and practice. Great stuff Daniel.
Curtis W (EHA3)

Very interesting article. I'm already trying it myself.

CHol (EHA3)

Great article. I will probably read it a few more times.

I am not sure if I 100% understand what's in the article. The dynamic ranges are very useful to me as I have some experience in Excel VBA. It speeds up a lot work if the Excel do the calculation on the range since it is always changed from time to time.

After reading this article, I'm mentally reviewing all my past work with excel... I see a lot of usage for the Dynamic Ranges!!!

Guilherme.Brito (EHA3)

A great article. I've used Index instead of Vlookup at times but I never knew I could use it to create a dynamic range.

Bob Kennedy (EHA3)

A lot of information. I really need an example file for me to fully grasp all of the concepts.

Daniel,

What an eye-opener. I had been a big fan of INDEX for years, but have recently been moving away from it in favor of OFFSET. I realize now that I've been under-utilizing INDEX all this time! I'm going to have to do some serious re-evaluation of my models.

Thanks! Rm

Wow! Such versatility! It took me 3-4 reads to begin to get it but I think I'm nearly there...a few working examples would be great if you've got them.

I, like some others in the forum, have been an avid user of OFFSET for dynamic formulas. In a big worksheet, you're right, it is clunky! Time to see what difference INDEX makes...thank you!

I had read this article when it was first posted; it was one of the things that drew me to sign up for the Excel Hero Academy. I am still working on getting my head around the possible uses.

Steve Porter (EHA3)

Oh, the places my data will go...

Where has the imposing INDEX been all my life?

Week 1 of EHA is already a win for me, looking forward to even more terribly useful info to store in my brain!

Sara Goecke (EHA3)

Wow, this is great! Had to read over the dynamic range section several times to make sure I understood the syntax, but this will be really useful for date-driven chart ranges.

Great article! Very good info and great uses for the Index function.

D_BELLIZZI

Daniel, Great article. I really liked the way you took a complex abstract construct like INDEX and broke it down step by step so even a rookie like me could begin to understand it. To use a football analogy since me team the NY Giants just won the Super Bowl, in oreder to score the winning touchdown you need to understand and master the fundamentals of blocking and tackling, or to belabor the analogy even further when the tourist asked the New Yorker how do get to Carnegie Hall? the New Yorker reply, Practice, practice, practice.
Al
EHA3
Student

Very useful knowledge here and I spent a lot of time at the SUMPRODUCT article also.

Boby
EHA3 Student

(pj1001 at EHA3) My brain hurts.
http://www.youtube.com/watch?v=IIlKiRPSNGA
Tomorrow I will be able to comprehend it better.

Daniel you create an respect and awe around what I thought was a pretyy mundane function, until now.

Hi Daniel and fellow EHA students.

Each time I read this article, I get something more from it. The stand out this time is using the index in an array across a range... ex. {Index(data,,{3,4,5,6,7,8})} entered once, versus entering it seperately for each column like Index(data,,3) in one column, Index(data,,4) in another, etc.

Very cool.

ps. I'm going to play with the area peice... just to see what I can do with it!!!

I have used INDEX a little in the past as a substitute for VLOOKUP. I'm looking forward to exploring it a lot more now!

Great post! Which others articles related to this application do you recommend?

Though =INDEX is still somewhat imposing to me, my dynamic ranges (i.e., a named formula intelligent enough to refer to the range I care about) will never be so much better.

I remember reading this when it was originally posted and thinking "Wow". Now, after re-reading as a member of EH3 ... same reaction. I've only scratched the surface of what this can do.

Index function is absolutely impressive. As well as this article. Somebody has already told this, but it is true. Each time a read this article I learn something new.

Good read and examples that are easy to follow with excel open to practice.

After a long time trying to convince my colleagues of OFFSET and INDIRECT..."In fact, the improvement in performance is so great that INDEX should be the foundation of all dynamic ranges in professional models."

And I had been using INDEX and MATCH for long! What I did not know is that INDEX returns a reference. Well, I did not know what a reference was, at least in its whole sense. This article was really inspiring when I read it, and still is every time I do it again.

Unai (EHA3 student)

Hi Daniel,

1st thx for your arcticle. But one question remains with me:

How can I use INDEX to substitute INDIRECT, and especially can I create dynamic references to other tables?

Example:
A1: = INDEX(Sheet2!A1,) -> OK

A2: = INDIRECT("Sheet"&2&"!A1") -> OK

A3: = INDEX("Sheet"&2&"!A1",) -> Not OK

So is there a possibility to get A3 working?
I already tried out ADDRESS within INDEX, but it didn't help.

THX Rob

Hi Daniel, thanks for the article.

Will need to read for a few more times, to really understand it.

I think now i know why my excel often shows (not responding) status.

I've been using lots of VLOOKUP to connect datas from different workbooks.

Leonard
EHA3 Student

Hi Daniel, Excellent article.

John
EHA3 Student

We straddle lots of data sets at our company with few common fields of information among them. Am clearer now that we are not using INDEX as competently as we might. Will take some time to digest it all and see it in action but know already it will make a big difference for us! Thanks! : )

Kathleen
EHA3

=INDEX((data1,data2,data3),,,2)...returns a reference to data2. And

{=INDEX((data1,data2,data3),,1,3)}...returns the entire first column of data3.

I THINK I followed most of this article except the above:

In the first example, I don't understand what the commas represent such that the "2" at the end asks for a result that is "data 2". What are the spaces between the first and second, second and third commas for? What would be returned if we populated them?
In teh second example, it seems the space between commas 2 and 3 indicates a Column # and the "3" tells Excel which of the indexed items to return. So what is the space between commas 1 and 2 for? Is it for a Row Index?

Thanks
Michelle

Thank you for the article. I think I will need to review a couple more times and practice some of the concepts in excel to hammer it home. I can certainly see where some of the points listed will help with existing excel reports using more basic logic.

Great article. I usually use OFFSET for my dynamic ranges, but from this will begin to implement your instruction.

Thank you

Thank you for the information. I've never really used INDEX before before but will definitely use it now.

This is the article that got me to sign up for EHA.

Thanks

I've always used offset for dynamic ranges, and glad to learn something new.

When defining a two dimensional array you used the following formula.
d: =$A$2:INDEX($1:$65535,COUNTA($A:$A),COUNTA($1:$1))

The $1:$65535 array only extends to row 65535, which would cover the entire sheet in Excel 2003 and previous.

Is there a quick way to cover the entire sheet in Excel 2007+? Or do you still have to guess at potential size of the dynamic range?

Thanks,
Jon Acampora
EHA3 Member

I've been using Excel for 15 years, and I've mostly used INDEX/MATCH for doing "reverse lookups," i.e., doing a VLOOKUP right-to-left instead of left-to-right.

I feel like an Excel newbie after reading this. But that's a good thing.

thanks,
Geoff Lilley
EHA3 Member

My mind is racing right now with ideas about index. What a powerful tool and how nice that it has such a small footprint...

Del Turner
EH3 Member

I've used index for only the most mundane uses: simple lookups, two-way looksups, etc. After reading this, if you've built complex models, your mind begins to race with the power that this formula has. Great article.

CE
EHA3 member

I have used Index fairly often for standardising access into a range in excel formulae, pointing to required columns. However, clearly there are a multitude of uses and I look forward to re-reading Daniel's explanation a few more times and experimenting with the possibilities.

John Pomfret
EHA3 member

Excellent!

I have used the Index function for a while now. The most common reason I use it is for finding intersections of data within financial spreadsheets. For example, given a month and an account, I can find the amount spent/earned. This allows me to reduce and summarize the information very quickly. And I thought I was using the function to its full potential. Like I found out recently with the SumProduct function, there is so much more I can do with Index. Thanks!

I liked the post. Dynamic ranges have great potential to improve formulas in the models I use regularly.

Thanks,

Rhamey

EHA3 Member

Very insightful and well written article. It was easy follow and helped illustrate several potentials for my future use of the INDEX function.

Thanks Daniel!

Todd C.

I really liked the article; just one question in the formula =INDEX((data1,data2,data3),,,2) for Noncontiguous Areas if I named data1, data2 and data3 as a range (c2:e6-c5:e9-c10:e15, respectively) I got "#value!"; is that because each area in reference contains more than one row and column?
Then doesn't this formula work with ranges? why does it work as an array formula only? (I mean with ctrl shift enter)

Anabel Rdz

Thanks for sharing the advanced side of INDEX. I've been using it for a long time, but I learned some new applications and aspects of this outstanding function.

Thanks Daniel!

I have used INDEX for many years, but this article provided some hints and techniques I was not aware of. Thanks for the great material.

Never used INDEX before and am anxious to really start putting it to good use! Thanks!

Amazing and powerful.

Thanks Daniel,
Every time I read this this, I learn something new. What a powerful tool!

I never thought you could do so much with Index. I look forward to using this on a new report for work. This blog post os going to be a great reference.

Though I have used the INDEX function quite often, the way it is used in the article for dynamic ranges is new to me and opens up possibilities.

Wow...very Interesting stuff. I've got a lot to learn. Looking forward to it.

Jake, EH4

Daniel,

Some useful info on how INDEX works. I have used it to convert a square matrix to a list matrix using the MATCH function without really understanding how or why it works.

Toby
EHA4

As an avid user of INDIRECT and OFFSET for creating 'dynamic' ranges - all I can say is WOW! Time to start re-learning and re-constructing...

Andy
EHA4

I have always used vlookup when extracting data...Index seems to do the same job and more! I have to admit although the article was nicely written, at least for me, it was a bit hard to grasp...I needed visuals since I am not familiar with the formula.Other than that, it was good.

Very interesting. I'm now working to incororate this in my reporting for work.

Joe
EHA4

Very powerful. It will save me time in my work.

joowens
EHA4

I am a huge fan of VLOOKUP. I am interesting in understanding INDEX better. I can see how INDEX can improve many of my VLOOKUPs, but need to see it in action I think to really understand.
lneely
EHA4

I've used Index for a long time, but not dynamically until EHA. I've already started revised my worksheets.

Very cool. I use vlookup all the time. I have not used index too much but I can see where it would have advantages.

Wow. A bit overwhelmed...but learning

Great article. I've had limited exposure to the INDEX function...so I'm looking forward to better understanding the power and finding ways to incorporate it into my work.

I use vlookup all of the time, but can see that index has more power. I'm having a challenging time understanding the article, but plan to experiment with index and then read it again.

Great article. I am feeling challenged. Haven't used the index function before. Have used vlookup. Excited to learn. Like everything in this course, I need to re-read and practice it until I understand it better.

Nice article! A bit over my head at the moment but I will deffinately be replacing my VLOOKUPS with INDEX soon!

First week of EHA has been great!

Cool. Nice explanations. Since EHA1, I have been using dynamic ranges with index. Didn't think to use it instead of vlookup though. The bit about the noncontiguous areas was enlightening - I have needed to do that on charts before but had no idea how. Thanks!

Great start so far to the course!

Bart
EHA4 Student

Just reviewing this article and constructing a spreadsheet to work through it...
Got to the 'Sums' section and the SUMPRODUCT calcs - if the columns of 'd' are text headings the calcs produce #VALUE! errors.
Reason? The first row of the calculation is FALSE * a text value.
Delete the text headings of columns 1 & 4 and all is well.
Hope this helps anyone else struggling to get the answer... lol

I am looking forward to practicing with the Index function. I think I will find many uses for it. Great first week!

The single most important function in excel and I rarely use it. I guess I have some work to do.

Wow...lots to absorb. Looking forward to the challenges ahead!

A belly full to digest.

I am looking forward to using this function in my future Excel sheets.

Great Stuff! I'm really looking forward to using the INDEX function in the future.

great stuff! I can tell already that EHA is going to be awesome!

Thanks for sharing!
From now on I am going to more frequent use of the INDEX function in my worksheets.

Daniel,

Wow! An excellent and informative article that is written in a very articulate manner. I am looking forward to the work and growth that lies ahead…

Anthony, EHA 4

I have, with some support, used the index function in the past. It really is a great way to manupulate data for the ease of the user. Thanks

Excellent stuff. Thank you Daniel.

Simply stunning. I definitely have to think it through. No light coming on yet but I see the potential.

Very thorough and thought provoking. Even though I learned this already in EHA3... As I also found out in EHA3, I have so much to learn.

AD ("Just Reviewing" post) thank you, I ran into the #VALUE as well. I'll go back and try your solution.

Question: I am working through the ariticle and in the dynamic range part, I do no think the Field(s) need to be changed to dynamic; Country: =$A$2:INDEX($A:$A,COUNTA($A:$A)) becuase they refer to and are defined by "d" (data). Making "d" (data) dynamic; d: =$A$2:INDEX($1:$65535,COUNTA($A:$A),COUNTA($1:$1)) takes care of making all fields that refer to it dynamic as well?

Good info. I have worked a little bit with using Index and Match functions at work. Looks like I still have a lot to learn.

In many years of using Excel I have not even touched the INDEX function and now you describe it as the single most important function ;)
I guess I have some catching up to do... Thanks.

Bermir, EHA4

I'm with Bermir.
I think I understand the very basics of INDEX...but not the section below. In some videos, Daniel has provided the translation of formulae or code, "How Excel thinks of it".
Can the same be done here?
How is Excel interpreting:
=INDEX(A1:C5,1,3)
{=INDEX(A1:C5,0,3)}
{=INDEX(A1:C5, ,3)}
such that all 3 return a reference to C1?

Thanks
Michelle

Interesting article. INDEX is the key to solving a to of my problems!

Andre M. Grangeiro

I feel like I've just touched the sleeve of something big. Know this will be intimidating for some of our (company) class members who are less familiar with Excel, because I'm intimidated. But it's a start... ;0)

Interesting article. I'm sure my appreciation will grow as I expand my use of Excel.
Thank you,
Tyler

Although I am aware of Index I am beginning to think that I only know a small portion of its capabilities. Excited to use this one more and learn where it can speed up my models.

I have so much to learn in such a short time. I am thinking of the uses of this in past projects which I will need again in the future.

I am so ashamed that I did not use this wonderful function before. Most of my workbook are built using the vlookup and are therefore very slow to recalculate. I just signed in for the Excel Hero Academy, almost finished the first module and I have learnt more here in a few days than in years working by myself. I am very excited about using the index function in my current and future projects. I also thank you for the information on how to discover what symbols are used in international version of Excel. I could not get my formula to work, I was sure the column separator was ";" like it is in every formula I know in "French" Excel... but following your vba instructions I discovered it was "." and therefore was able to make things work. I learn new things everyday, Merci !!

Nice, clear instructions for Index.
DMiller, EHA4

After several readings I am finally grasping the power of INDEX. Thanks for sharing your knowledge.

Mari

Every time I read this (4x now) I seem to pick up a new use for INDEX
Darren, EHA5

I've read it.. think I'll have to read it 4 times like Darren :) to wrap my mind around this function and its potential. I'll try and not use my beloved VLOOKUP() and start using =INDEX(MATCH()) instead.
Jon EHA5

Some great information that I'm sure I'll need to practice before I can fully understand it.

I started using INDEX(MATCH()) about 3 months ago because VLOOKUP would not handle multiple variables. I almost did my head in. Thank you Daniel for such a wonderful article that gives me a better understanding of how I can now improve not only the formula, but also the efficiency of the file.

Clare - EHA5

I have not used INDEX before, but will start soon. I think I need to re-read several times to truly get the scope.

I look forward to the time that INDEX becomes as automatic as VLOOKUP has become over the years. I guess practice will make perfect. I especially like the use for dynamic ranges. I have used dynamic ranges to define a dynamic Print_Area and think the INDEX function would work better than the OFFSET method I used.

I also have not used INDEX before, but I will start using the dynamic range application immediately.

Dan - EHA5

Great article. I never needed to use Index too often as I can usually get by with sumifs and vlookups but I now see how helpful this can be.

I use vlookup heavily. INDEX is a super efficient alternative!

Hi, EHA5 student checkin in here

I never really used VLOOKUP. Chandoo taught me INDEX MATCH. And that was all I thought index could do. Great! Then I discovered how to leverage lookups using SUMPRODUCT. Even greater! Also OFFSET does the trick. But man, INDEX has a lot more to it, as you beautifully showcase in this brilliant article. Simply amazing!

If excel was a chessboard, I would say OFFSET is a horse, SUMPRODUCT a queen, but INDEX carries the sheer power of two towers covering a whole row and a whole column.

OMG!

A few months ago I actually found out about INDEX, and now it is my favorite! Great article to expand my knowledge, thanks!

Leslie-EHA5

A great article - makes it pretty easy to wrap your head around this function. I think it will soon overtake my use of vlookup.

Tammy

I have never used the INDEX formula before, but it is easy to see how powerful this formula can be. I can't wait to start working with it.

Matt - EHA5

This type of usage of Excel is new to me - my knowledge (after watching this module) is basic, but I am looking forward to expanding that knowledge. With that said, I am hoping that it will not take me too long to wrap my brain around these concepts. After learning what these functions can do - I am looking forward to becoming more comfortable with them as I have a yearly spreadsheet that I create for two departments and these will make those workbooks function much better. And I won't have to adjust the formulas for each sheet. I'll need some practice, but think it should sink in soon.

Alicia-EHA5

Having read the post several times now the mist is beginning to clear (to make way for a haze I wouldn't wonder) and I am looking forward to learning more.

John M
EHA5

Very interesting lesson, I have actually never used INDEX before I will try to start using that instead of vlookups which I use often. Some more business examples of when its good to use INDEX would help get the brain thinking on how to use it in my day to day items in Finance.

Since I'm fairly new to Excel as a whole I read this article a few times and think I understand the basic concepts of everything. As we move forward and I am able to practice a bit I think it will come easier to me. Looking forward to that!

I am very excited to use the knowledge I gained while reading this article. I had forgotten how nice the INDEX function was.

I haven't used INDEX before, I can see that it can be powerful but not real sure how to apply it to the spreadsheets I work with.

This is great. I've used INDEX(MATCH) a lot but creating named formulas and dynamic ranges will make updating these reports a lot simpler

As a frequent user of VLOOKUP I found this extremely interesting. I'm excited to see how I can leverage this at work.

Best,
Jamie

(EHA5)

This index booklet will become a bible of sorts for this class and future work. Had I gotten far enough in John Walkenbach's book 11 years ago to understand how powerful this is, it would have limited my need for a lot of really inefficient VBA programming.

I've read this artical, tried a few things to test the examples and am not quite grasping it. I reviewed Module 1 several times as well and seem to have acquired some understanding, hopefully it will become clearer as we go along.

I have read the article and although I am beginning to grasp the concepts, I still have much work ahead in order for me to apply the concepts to work and study projects.

At my workplace we spend much time manually updating charts for reporting of rolling time periods. My plan is to explore the 'Dynamic Range' concepts to see if I can apply them to charts where data series are dynamically 'selected' based upon an Index function on both sides of the ":" Reference Operator.

I envision an Index function on the left side of the ":" that subtracts to 'count back' the correct number of cells from the end of the Index to represent the 'start' of the series. I envision an Index function on the right side of the ":" that will dynamically increase the size of the range as an additional row of data is added at the end of a reporting period.

I may be writing this prematurely as I have not yet worked to implement. I do welcome any thoughts or feedback that anyone may have.

Anthony (EHA5)

Leave a comment

About this Entry

This page contains a single entry by Daniel Ferry published on March 21, 2011 3:29 PM.

Excel Dynamic Checkmark was the previous entry in this blog.

Excel Hero LinkedIn Group! is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.