The Most Useful Excel Function You Will Ever Learn

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 that 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:

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!