25 Jan 2016

## Index of Ignorance

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) 