25 Jan 2016

I did a quiz on The Guardian’s website called “How well do you really know your country?” The pic I saw after the quiz reminded me of Excel’s INDEX function. Yes, I see Excel function names everywhere I go!


The combination of “Index” and “Ignorance” reminded me about two ways to use the INDEX function that I learned last year from John Michaloudis and Mike Girvin.


John MichaloudisJohn Michaloudis: Sum a Range Using INDEX Function

Here is John’s formula: =SUM(INDEX(C12:E21,,2))

John wanted to add all numbers in column D within range C12:E21. By skipping the row number (typing consecutive commas) John gets all the numbers from column D (2 = column D).

Read John’s post to learn more. I would normally solve this using OFFSET like this =SUM(OFFSET(C12,0,1,10,1)) but I think John’s method is better.

Note that in both formulas we could add a MATCH function instead of the hard coded column index number (the ‘2‘ in John’s formula and the first ‘1‘ in my formula)


MikeGirvinMike Girvin: The other way to use INDEX function

It took me awhile to re-find this video and it’s great! Mike demonstrates the ‘other’ way to use Index function (2nd method using ‘reference‘).

INDEX reference area_num

I won’t re-explain how it works (watch Mike’s video) but it’s cool how you can use the reference parameter to list various independent ranges.

Here is the formula: =E2*INDEX(($B$2:$B$5,$B$10:$B$13,$B$18:$B$21),MATCH(E2,$A$2:$A$5),,F2)  Mike points out that this formula also works and is probably better =E2*VLOOKUP(E2,CHOOSE(F2,$A$2:$B$5,$A$10:$B$13,$A$18:$B$21),2) but it’s good to understand how the 2nd method of Index works.

I was just playing around and it’s possible to put the list of ranges in a named range.

Formula looks like this: =E2*INDEX(RangeList,MATCH(E2,$A$2:$A$5),,F2)   ‘RangeList‘ named range =Sheet1!B2:B5,Sheet1!B10:B13,Sheet1!B18:B21

I’m not sure why we would need to do this but it works.


Watch Mike’s YouTube Video (Excel Magic Trick 778)

(the video starts at the relevant point)

About Me

My name is Kevin Lehrbass. I live in Markham, Ontario, Canada.  I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst at McKinsey & Company.

Hundreds of millions of people use Excel everyday. Thousands of hours can be saved and costly errors can be avoided or corrected if you study this powerful software. Check out my videos and my blog posts.

Away from Excel I enjoy playing with my dogs Cali (she doesn’t like Excel) & Fenton, learning Spanish and playing Chess.



Post a comment