the CHOOSE function

Just so we're clear on the lingo: a not contiguous rank (with reference to Excel) means a range that cannot be highlighted by mouse alone. In the picture above, to highlight colored cells, I would have to press the button CONTROL key also.

INDEX, SEARCH FOR, VLOOKUP Y HLOOKUP all require contiguous references. They refer to lists, row vectors, column vectors y / or matrices. CHOOSE It is different:

As previously explained, this function allows references to different calculations, book references / work sheets, etc. Try to use the function properly. As an example, a popular Excel web portal proposes the next formula to calculate the US Thanksgiving date. UU. Assuming cell A1 has the year:

To understand this formula, note that DATE (year, my, day) returns a date and WEEKDAY (date) returns a number from 1 (Sunday) al 7 (Saturday). But doesn't this formula look horrible? It is packed with hard code and contains an unnecessary amount of arguments. The formula could exclude CHOOSE verbigracia.

Now let me be clear here. I am not saying this is a simple and transparent formula. test it. They both give the same answer. CHOOSE – and a lot of additional physical code – has been used unnecessarily.

That is not to say that there is not a time or a place to CHOOSE. It is useful when you need to reference cells in different worksheets or in other workbooks. Some argue that it is useful when a calculation needs to be calculated using different methods., p.ej

I do not agree. Let me explain. In the following example, I have created a lookup table in cells E10: E13 what have i called Data (I will explain how to create range names later). All calculations are visible in the worksheet, instead of being hidden in the formula bar. The index_num selection, here referred to as Selection_Number, is entered in the cell E2. The result?