- BY Kevin Lehrbass
- POSTED IN Excel Gurus, Tips
- WITH 0 COMMENTS
- PERMALINK
- STANDARD POST TYPE

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