Excel Tip: Using CHOOSE – or do you need OFFSET, or maybe INDEX?

From the Not Just Numbers blog:

How are those New Year Resolutions coming along now we’re all back to work?

Did you set your Excel ones? I’d still love to hear what they are.

Just a couple of quick messages  before we get into today’s post.

First of all, if you’re not a subscriber to the blog and therefore didn’t get my exclusive Christmas video showing you how to use drop-down lists, I have decided to give you one more chance. I will send it out again at the weekend to any new subscribers that have signed up by the end of this week. So get yourself subscribed now in the box at the top right of the blog – if only to laugh at me in a Santa hat!

Secondly, I have made a few changes to the colour scheme of the blog – to freshen it up for the New Year. I would really appreciate any feedback on the changes, both positive and negative – as well as any other suggestions for changes to the layout of the blog.

Well, on with the post…

Tom, an accountant in Australia and regular Not Just Numbers reader, asked the following when I asked to hear what you wanted to learn from the blog:

“I’d like to learn about the CHOOSE function and using this for selecting data from another area of a worksheet (I am not sure if this would be used in conjunction with OFFSET?)”

Well Tom, the CHOOSE and OFFSET functions both perform a similar function – but in different circumstances.

I have covered the OFFSET function before, so I thought I would introduce the CHOOSE function and then explain how to determine which one you need. And then show you why INDEX might be better anyway!

The CHOOSE Function

The CHOOSE function is pretty simple and is used to select from a fixed list as follows:

=CHOOSE(position in list,item1,item 2, etc.)

so =CHOOSE(3, “A”,”B”,”C”,”D”) returns C, being the third item in the list.

The list can contain up to 254 items.

You could use cell references as the list items, e.g.

=CHOOSE(3,A1,B1,C1,D1) will return the contents of cell C1.

Or you could even use ranges as long as you told Excel what you wanted to do with the range, e.g.

=SUM(CHOOSE(3,A1:A3,B1:B3,C1:C3,D1:D3)) returns the sum of cells C1 to C3.

The OFFSET function

I will not go into the full workings of the OFFSET function here as you can read about itin my earlier post.

You could though use the OFFSET function for either of the last two options:

=OFFSET(A1,0,2) returns the contents of C1

(notice an offset of 2 columns returns the third item here as the first item (A1) would be an offset of zero columns)

=SUM(OFFSET(A1,0,2,3)) will return the sum of the range C1 to C3, being offset by zero rows and two columns and having a height of 3.

OFFSET can be far more flexible and and easy to use, particularly as the list gets longer., however will not work if the list contains ranges of differing sizes (in which case you would need to use CHOOSE).

In most cases when the items on the list are held in the spreadsheet, OFFSET will be the better choice – except where you need to select from different size ranges as mentioned above.

The INDEX function

The INDEX function is an alternative to OFFSET which can be a little more complicated to understand, but  makes a better use of resources – which is particularly important if your spreadsheet is getting large and cumbersome.

INDEX has two forms, but for this purpose, we only need to worry about the more common one:

=INDEX(Array,Row Number,Column Number (optional))

The function then returns the value from the array at the intersection of row and column. If the array is only one row high or one column wide then you only need include either the row or column number. Also, a row or column number of zero will return the whole row or column as a range.

So…

=INDEX(A1:C1,3) will return the contents of C1

and…

=SUM(INDEX(A1:C3,0,3)) will return the sum of cells C1 to C3

I hope that helps you Tom, as well as everyone else!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report “The 5 Excel features that you NEED to know”.