The Most Useful Excel Function You Will Ever Learn

by Kien Leong

Index Excel Formula for Software built by Spreadsheets

I would like to introduce the most useful function that Excel has to offer.

This is the most widely used Excel function in the Fast Excel Development Method.  When building planning systems,  I find myself typing this in formulas more than any other function.

The function can transform Excel spreadsheets into a fully-blown business software platform.  We use it to retrieve data, perform calculations and present them in a report.

 

The magical function is INDEX.

Even if you are familiar with INDEX and have used it before, I bet I can reveal some tricks that you may not seen .  I consider myself an Excel power user, yet I am sure that some Excel guru in the comments will give me a new tip on using INDEX that I do not know.

Such is the power of INDEX.

What Does INDEX do?

INDEX returns a value or reference to a cell, based on a coordinate in a range.  Here are the inputs:

=INDEX(Range, RowNumber, ColumnNumber)

The range can be one dimensional (A1:A8) or two dimensional (A1:C8)

Let’s look at an example.  Here is a simple table Example Excel Table for INDEXthat describes quantities of fruit on certain days of the week.  If I apply the formula:

=INDEX(B1:B8, 4)

it will look down range B1:B8 for the 4th cell and return its value: “Oranges”.

=INDEX(A1:C8, 5, 2)

will return the 5th row and 2nd column of the whole table A1:C8: “Bananas”.

You can also use complete column references (A:A),

or named ranges, eg.  “Fruit_Table” where Fruit_Table = Sheet1!$A$1:$C$8

or Dynamic Named Ranges

Fruit_Table=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1).

More on Dynamic Named Ranges below.

The Most Common Application for INDEX

INDEX is most widely used as part of a Lookup formula.  Many Excel users are familiar with VLOOKUP to return a value from a table when you have another value from the same row.

When combined with MATCH, INDEX is a far superior method.  Here is how it works:

INDEX MATCH Lookup example

MATCH looks for a value in a range and then returns its position.  The result of MATCH is a number.

Say we wanted to know which fruit was consumed on Wednesday.

=INDEX(B1:B8, MATCH(“Wednesday”, A1:A8, 0))

=INDEX(B1:B8, 4) = “Oranges”

A combination of INDEX, MATCH is used all the time in spreadsheet applications to retrieve a value from a table.

Now, why is it better than the old trusty VLOOKUP?

For a start, it is much faster.  Most Lookup jobs need more than one value from the same table.  The MATCHing job is the computing intensive, but we only need to do it once.  In one column we can return the row number and apply this to multiple INDEXes.

VLOOKUP is also inflexible as it depends on returning a column that is relative to the table.  If you don’t believe me, try looking for a value that is to the LEFT of the Lookup column.

INDEX can return both row and column values, so you could use it to find a value in a table using both the field name and the Lookup value.

=INDEX(Fruit_Table, MATCH(“Wednesday”, A1:A8,0), MATCH(“Fruit”, A1:C1, 0))

Getting More Advanced Excel with INDEX

For the real power of INDEX, we can get a clue from the definition

“INDEX returns a value or reference…”

This means it can retrieve a value from a cell – “Oranges” – or the cell reference – B4.  There are are only a few Excel functions that can can return a reference. (INDIRECT, OFFSET being the notable others).

If we can get a cell reference, then we can use it as a substitute to a cell address.

=INDEX(C1:C8, 2 ) = C2

=INDEX(C1:C8, 8 ) = C8

Therefore

INDEX(C1:C8, 2):INDEX(C1:C8, 8 ) = C2:C8

and SUM(INDEX(C1:C8, 2):INDEX(C1:C8, 8 )) = SUM(C2:C8) = 39

This method is powerful because it allows you to reference a range using formula.  Combined with a SORT procedure, you can use INDEX:INDEX to find a range that represents a subset of a long column.

Here is an article on using SORT and INDEX for fast analytics on 65,000+ rows of data.  SUMIF, COUNTIF etc would be excruciatingly slow on large tables.  SORT and INDEX both work lightning fast and can out-perform calculations that have search and calculate values in long columns of data.

Index and Dynamic Named Ranges

Dynamic Named Ranges are critical to automating Excel.  They grow and shrink to fit the data in a table.  This means that when you connect Excel up to a host system and the input data is always changing, the table reference changes with it.

For a full description of Dynamic Named Ranges, subscribe to our Fast Excel e-Learning.  This will show you how to use this technique to build powerful planning and scheduling systems that can process a dynamic flow of business data.

However, you can see a good introduction to Dynamic Named Ranges at OZGrid using OFFSET.  Daniel Ferry’s ExcelHero has a good take on building Dynamic Named Ranges with INDEX.

You could also build a dynamic named range that referred to only the Day column (A1:A8) using either OFFSET or INDEX

=OFFSET(A1, 0, 0, COUNTA(A:A), 1)

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

Take your pick.  We use OFFSET in the Fast Excel Method as a convention.  I have not noticed much of a performance difference.  If your experience suggests otherwise, I’m open to correction.

INDEX in an Excel Application

Most business applications work by retrieving data from tables, performing some calculations and then presenting it to the next process.  Business software usually sits on top of large relational databases, and needs to grab data records quickly from these tables.  INDEX replicates this database function, only from spreadsheet tables rather than databases.

In the Fast Excel Development Method we urge you to separate data storage, calculation and reporting.  Otherwise you make the biggest mistake in using Excel for any kind of automated business application.

The data can originate in a database, get pulled into Excel by text file or ODBC query.  Once the table is in a spreadsheet, INDEX and MATCH can get to work.

INDEX is the enabler to move data from a store, through calculation tables and then into a final table that feeds a report.  Master it, and it will be your favourite spreadsheet tool.

Any other useful applications for INDEX?  Please let us know in the comments below!

{ 18 comments… read them below or add one }

subrahmanya manas pappu June 30, 2011 at 1:26 pm

Sir,
Index truly is a wonderful function. i used the same in place of vlookup and it has created wonders with all the retrieving.

Michael Filgate June 30, 2011 at 5:50 pm

Index and Match are incredibly useful in creating custom data validation lists, and dropdown lists that depend on another cell. Refer http://www.contextures.com/xlDataVal13.html

Kien Leong June 30, 2011 at 7:12 pm

Michael

Thanks for this. It is a useful extension to using dynamic named ranges with data validation. It could be used to save a lot of user time by creating smart data input fields in this way. I appreciate the link.

Kris July 1, 2011 at 1:33 am

To my opinion the following formula should be corrected in your article:
=INDEX(A1:C8, MATCH(“Wednesday”, B1:B8, 0) –> you look for Wednesday in the fruit column

=INDEX(A1:C8, MATCH(“Wednesday”, A1:A8, 0) –> now you look in the correct day column

Kien Leong July 1, 2011 at 2:04 am

Correct! Many thanks, Kris

Michael Haynes October 31, 2011 at 7:50 am

This article transformed the way I use excel. It freed me from the prison “lookup table prison”

Thanks again

Declan McTiernan October 31, 2011 at 7:57 am

This article has allowed my spreadsheet solutions to work so much better than before. Thanks

Manu October 31, 2011 at 8:55 am

Great article, I’ve learned several ways to use this function!

James Knowlson October 31, 2011 at 12:08 pm

This for me has been the most useful. In the past using vlookup had reduced my spreadsheets to a snail’s pace, especially on some of the larger databases. Now I know this there’s no more waiting around. I’m still impressed with this function every time I use it. MATCH INDEX is the way forward!

Mohsin Zuberi October 31, 2011 at 11:13 pm

This article has showed me a better & effective way to retrieve data from lookup tables. Great Job!

KEL PHIRI November 1, 2011 at 12:36 am

Kien this article is awesome, made very good use of the info.

cheers

Otis December 27, 2011 at 10:21 pm

How does this differ from the “SUMIF” function?

I normally use value in the far left column as my “criteria” and the far left column of my data source as the “range”, then for each column going to the right which requires a value, I’ll increase the “sum_range” by one column.

Eg: I want to know how many apples and oranges were consumed Sunday through Saturday. In my source data, there is a row representing every consumption of an apple or orange (Column “A” is my “Range” and indicates which one is being consumed (there is a row for every consumption, so you have as many rows as your total quantity of apples or oranges consumed, Column “B” is Sunday, Column “C” is Monday, etc….

If I wanted one line for each item consumed, I would enter each item into column “A” on a new spreadsheet with a row for the headers and a row for every item. Example cell A1= “Item”, cell A2= “Monday Consumption Qty”, and A3= “Tuesday Consumption Qty”.

I would manually (or C&P) the info so that Cell A2= Apples, Cell A3= Oranges

Cell B2 would house the following forumula=sumif (Source data $A:$A, $A2, Source Data B:B).

Cell C2 would house “=sumif (Source data $A:$A,$A2, C:C)”

And so on and so on.

Kien Leong December 28, 2011 at 11:56 am

Hi Otis,

SUMIF is a useful function. INDEX is quite different as it returns a range reference, not a sum value.

However, even for SUM functions, SUMIF suffers when dealing with large tables of data. Faced with many criteria options and 10K+ rows, SUMIF can grind to a crawl. We can even use INDEX to reduce the calculation load on conditional sum functions. See here for details: http://production-scheduling.com/fast-formulas-excel-demand-analytics/

Jesus Lara January 15, 2012 at 2:42 pm

Really useful, I am running my production scheduling with vlookuup, it works, but you are right, it’s seems easier with index.

Sergio Nolte January 22, 2013 at 7:36 am

Kien, this was an excellent article on the INDEX function and its different applications when combined with other functions. Thank you!

Prashaanth April 17, 2013 at 4:42 pm

hi
I need to develop a live feed data for my production line using excel which will schedule everyday’s work and give live feed. This is more of production scheduling

can u please help ?

Thanks

Jeanie Burdi July 25, 2013 at 9:58 pm

I have tried to understand this for a long time. I have played with it in the past, but just couldn’t grasp it.
Kien Leong you have an excellent way of breaking this down.

Thank you so much… I am sure I will remember this for a long time now. It is like when I first learned VLOOKUP, but better. :D

Stephanie Bauman September 17, 2013 at 3:26 am

So, this is the most complex way I’ve used the INDEX function. You have to enter it as an array formula and it can be used with multiple criteria, but here it uses just one. What it does is return the first instance of an item in the range that matches the criteria used in the formula. When copied to the cells below, it returns the next item in the list to match the criteria, and so on. I have to admit that I stole this from someone else and know how to manipulate it but don’t really know why it works. :-) So, let’s say in this example, Friday was also a day for Apples and you’re putting this formula in cell D3.

=INDEX($A$1:$A$8,SMALL(IF($B$1:$B$8=”Apples”,ROW($A$1:$A$8)-ROW($A$1)+1),ROW()-ROW($D$3)+1))

This returns the result of “Monday.” When copied to D4, it returns the result of “Friday.”

Notice that the ROW formula at the end cites the row of the calculated data, not the source data (you could use $A$4 also).

This has become a hugely powerful tool for me in tracking different categories of data in tables of up to a couple thousand rows using named ranges. It is more presentable and manipulable than a pivot table, even though I love pivot tables, too.

The limitation is that, as the list keeps growing, you have to make sure you’ve copied the formula down far enough or not all of the data will appear. I usually use the IFERROR formula to return a blank cell instead of the ugly #NUM once the data runs out.

Ok, I’ll shut up now.

Signed,
Excel Geek

{ 3 trackbacks }

Previous post:

Next post: