As part of Excel ‘Lookup & Reference’ functions, we have already seen few details about LOOKUP & VLOOKUP in previous posts. In this post let’s see one more useful function under this category i.e. CHOOSE.
CHOOSE returns a value from list given as argument based on the position of the value in the list. For example, if you have 10 values in a list and want the value in 8th position, you can use CHOOSE function. Function syntax is below.
=CHOOSE (Index_num, value1, [value2],…..[value254])Index_num: Is the position you want to return the value from.
- If index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on.
- If index_num is less than 1 or greater than the number of the last value in the list, CHOOSE returns the #VALUE! error.
- If index_num is a fraction, it is truncated to the lowest integer before being used.
Value1 to 254 are the list items. Value1 is mandatory and all others are optional. So you can provide upto 254 values to choose from. Note that the limit is 29 if you are using Excel 2003 or earlier versions.
Example: Let’s see a practical example. One of my clients runs a business. He uses 2 rebate tables for his customers. Table 1 is for normal customers. Table 2 is for frequent / premium customers. Both rebates are based on the sales value, but % of rebate is more in Table 2. Here are the tables.
Now when he bills, he has a field to select if the customer should be given rebate under Table 1 or Table 2. What he wants now is when he selects the applicable table number, the rebate % should be auto picked. We already know that we can use VLOOKUP to pull out the rebate but that only works with one array. Since we have 2 tables, we have to use nested formula using IF and VLOOKUP functions. Sales amount is in cell B10. I need rebate % in D10. Formula looks like this.
This works pretty well within the given example. However what if you have multiple numbers of tables and multiple conditions to check with. You have to nest series of IF functions one in another and add that many LOOKUP functions. Your formula becomes gigantic and even you won’t understand it on a second look. An easy way around is CHOOSE function. Formula looks like this.
You just need to enter your table ranges inside CHOOSE function as values and let VLOOKUP pick the correct one based on the table to be applied.
Let’s analyse our function. VLOOKUP looks for sales which is in B10 and as a second argument CHOOSE function will decide which range VLOOKUP to look in. Based on the table number selected in C10, it picks either A2:B7 (first table) or D2:E7 (second table). In our fist example, we selected table number 1 so A2:B7 becomes the second argument for VLOOKUP. It returns 2nd column from the array which is rebate %. As a last argument for VLOOKUP we gave 1 i.e. approximate match.
Like most of the Excel functions, CHOOSE is more powerful when combined with other functions like VLOOKUP, SUM, MATCH etc. CHOOSE function looks less messy if you give names to your tables and use named ranges. We will talk more about named ranges in future posts. Happy learning!!
