When would someone use the Choose function?

Hi, I've just stumbled upon the Choose function for the first time. I understand the concept but I can't see a practical use for it. Take the following

=Choose(3,"Sunday","Monday","Tuesday") Returns "Tuesday"

I cant see the benefit of it. Would someone be able to post a scenario when it would be useful please?

Thanks
John

I love the "ad nauseum" bit !

You can avoid errors (like Donkeyote does) link in this example:


the use of CHOOSE allows you to build an array of 2 values against which you can search according to the criteria value (in our case Z repeated 255 times)

=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",formula result))

The first value in the 2 value array is always a Null (text), the second is the variant output of the formula (may be anything - text or error).
One of the advantages of LOOKUP is that it will ignore values in the array that do not match the type of the criteria, this includes errors...
By using a BIGTEXT type criteria value the LOOKUP will return the last text string it finds in the 2 value array, ie:

=LOOKUP(REPT("Z",255),CHOOSE({1,2},"","Apple"))

will return Apple

=LOOKUP(REPT("Z",255),CHOOSE({1,2},"",NA())

will return Null as #N/A is ignored given it is not text like the criteria.

@martindwilson: LOL

@johncassell:

Or this mine solution for (I think) some score output for clubs (1, X or 2)

http://www.excelforum.com/excel-gene...f-formula.html

Needing to create an IF formula which would give the following as the outcome.

e.g. IF c2>E2 the outcome would be given as 1, if C2<E2 then the outcome would be given as 2 and if C2=E2 then the outcome would be given as X.

Any help appreciated.


=CHOOSE(SIGN(A1-B1)+2, 2, "x", 1)

Thanks, I think I see now. So its maybe more a tool for people who don't really know formulas. We can provide them a list of numbers to select from instead of them having to enter a new formula.

Cheers, really helpful
John

Can't get ddll's solution to work. get an N/A answer although x exists in D col

How does choose treat the backslash?

Thx ddll that is what JB used. But I don't understand how it works and in particular the part between brackets

Thx for the help JB. The use of the backslash in my regional settings was what bothered me most. Anyway, it's an inventive solution, I must say.

Thanks Gurus thats a very good read..
Never knew that one could tame the most popular VLOOKUP to give the value from the WRONG side..

Originally Posted by JBeaucaire

CHOOSE() normally expects a single answer in the first parameter. From that number the "option" further in the parameters is "opted for".

This construct uses an array in the first parameter which basically says, "choose 1 and 2".

Since "1" refers to a range of cells in column C, it is "grabbed" first, then the "2" refers to a range of cells in column A, so it is grabbed next.

Then the magic, the two arrays are "mixed" creating an on-the-fly array of paired values. C1 pairs with A1, C2 pairs with A2, etc.

Now that the paired arrays are created, the VLOOKUP finds the E1 value in the first range, then returns the matching value from the found pair.

It's pretty cool to watch if you click on the cell and use the Formula Auditing tool on it to watch it unfold one calc at a time.

That's so important to know..

Warm Regards
e4excel

******* Where would someone NOT use the Choose function? ***********
I was out curiosity looking on the web at the use of Choose function I never used before.

I was 'fascinated' by daddylonglegs' with the '=CHOOSE({1,2},D2:D1000,A2:A1000) + CTL + SHIFT + Enter approach.

So I tried it and played with it with success. Impressive. However, I could get the same results with a combination of vlookup & index function, in a limited test example. I still have to ingest where one is more appropriate / wider than the other.

But I tried Choose to solve a more mundane problem, trying to create dynamic data validation for a column in long worksheet, where a value in column n+1 depended on values in column n on same row, which led me to the following.

I just tried the following formula with Choose, where each of the 1 + 6 ranges to test correspond to names created referring to arrays of constant values in the workbook.

Here is the data validation formula for the list to pick the right range:

'=choose(MATCH(RC[-1],t_RS_NFR_Sub_Area_1,0),"",t_RS_Control_and_Processing_Risk,t_RS_Information_Technology_Risk,t_RS_Compliance_Risk,t_RS_Fraud_Risk,t_RS_BCP_Risk,t_RS_Employment_Practice_Risk)

Now, interestingly, after typing this data validation as an input for the list in data validation, it worked perfectly ... for that cell. If I copied it one by one, it worked also, perfectly.

But when asking to extend this data validation to all other cells with same data validation rule (ticking the box below the data validation window), it does it (it copies it fine in each cell of the column). However, when testing its effectiveness, it seems that Excel does not internally re-evaluate dynamically the content: it applies the same range to all rows the column, defeating the purpose.

So I went back to an old trick of replacing the data validation formula with '=indirect(RC[66]) where in column +66 (in this spreadsheet), I store in a work column the name of the relevant range depending of the value in column -1.

When I copy the 'indirect(RC[66]) function as data validation rule for all cells(rows) in the column, Excel re-evaluates this validation rule at each row (contrarily to the previous solution with choose, more elegant if it had worked). Both the formula was effective AND its copy to all cells with same data validation provided an effective solution.

I use RC instead of A1 referencing just to make it clearer for this example.

So, unless mistaken, this "inability to extend AND to use effectively Choose in this instance" seems an internal deep buried limitation of Excel 2010 when dealing with the data validation facility.

So johncassell raised an interesting question considering above responses. Also to get some support to his challenging comment (cf. "its may be more a tool for people who don't really know formulas") !, he may also be right at least in this limited situation. Thanks to all.