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 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)

Mike 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‘).

I won’t re-explain how it works (watch Mike’s video) but 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)

Save

Save