CHOOSE Function in Excel and its uses, make Vlookup Look Left & Find Date of Next Monday with CHOOSE function.
Related Links:
VLOOKUP formula that looks left instead of right.
————————————————————————————————————————–
Excel CHOOSE Function
The CHOOSE function returns a value from a list of values based on a specified position number.
Syntax: CHOOSE(position_number, value1, value2, … value 254)
position_number is a number between 1 and 254 which identifies the position of a value in a list of values. The position_number can be a number, a defined name, a formula or function, or a reference to a cell containing a number. Note that in Excel 2007 and Excel 2010 the maximum number of values can be 254 but Excel 2003 allows upto 29 values only.
If position_number is less than 1 or greater than the number of values in the list, the function will return the #VALUE! error value. If the position_number is a fraction, it is reduced to the lowest integer value.
If position_number is an array, every value argument will be evaluated while applying the CHOOSE function.
value1, value2, … value 254 are the list of values from 1 to 254 from which a value is chosen by its position_number. A value can be a number, a defined name, a formula or function, a reference to a cell or a text value. A value can be a range reference and also a single value..
Example 1:
=CHOOSE(3,B2,B3,B4,B5,B6) [Formula]
Returns Laurel, which is the value from the third argument B4. (Refer Table 1)
=CHOOSE(4,”Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday”, “Sunday”) [Formula]
Returns ‘Thursday’, which is the fourth argument.
—————————————————————————————————————————
Nesting CHOOSE function with other functions significantly enhances its usability.
Example 2:
=SUM(CHOOSE(2, A2:A5, B2:B4, C2:C3)) [Formula]
Returns 21, which is the sum of reference from the second argument B2:B4. (Refer Table 2)
Formula evaluates to =SUM(B2:B4)
————————————————————————————————————————-
Example 3:
=SUM(C2:CHOOSE(3, C3,C4, C5,C6)) [Formula]
Formula evaluates to =SUM(C2:C5)
Returns 77, which is the sum of C2:C5 where C5 is the reference from the third argument. (Refer Table 3)
————————————————————————————————————————-
Example 4 – make Vlookup Formula Look to the Left, by combining with CHOOSE function:
Composing a VLOOKUP formula that looks left instead of right: Vlookup C4 in Column A.
=VLOOKUP(C4, CHOOSE({1,2}, C2:C6,A2:A6), 2,0) [Formula]
Formula returns 14, the matching value from column A, in table_array. (Refer Table 4)
CHOOSE part of the formula rearranges order of columns in table_array.
Formula does a Vlookup on C4, choosing Column C as first column and Column A as the second column.
———————————————————————————————————————–
Example 5 – Find Date of First Monday in a month, or Find Date of Next Monday, by combining with CHOOSE function:
Find Date of First Monday in a Month:
=DATE(YEAR(A1), MONTH(A1), CHOOSE(WEEKDAY(DATE(YEAR(A1), MONTH(A1), 1)), 2,1,7,6, 5,4,3)) [Formula]
Formula returns 9/6/2010, date of first Monday in the month of September, 2010. Cell A1 contains the date (9/15/2010), which is referred to for determining month and year for which the first Monday date is calculated.
DATE(YEAR(A1), MONTH(A1), 1) returns the first day of the month and year of the date entered in Cell A1.
WEEKDAY function returns the day of the week corresponding to a date. WEEKDAY(DATE( YEAR(A1), MONTH(A1), 1)) returns the value 4. This means that the first day of the month (ie. 9/1/2010) is a Wednesday (Sunday is 1, Monday is 2, Tuesday is 3 & Wednesday is 4, …Saturday is 7).
The sequence of “2,1,7,6,5,4,3” relates to Mondays, relevant to the corresponding Weekday viz. If 9/1/2010 is a Wednesday, its Weekday is 4 and CHOOSE function chooses the 4th value in the sequence, which is 6 and the formula returns the date 9/6/2010. If 6/1/2008 is a Sunday, its Weekday is 1 and CHOOSE function chooses the 1st value in the sequence, which is 2 and the formula will return the date 6/2/2008, as being the first Monday of the month.
To Find Date of First Tuesday in a Month, use the formula:
=DATE(YEAR(A1), MONTH(A1), CHOOSE(WEEKDAY(DATE( YEAR(A1), MONTH(A1), 1)), 3,2,1, 7,6,5,4)). Note the change in sequence of nos. to “3,2,1,7,6,5,4”.
Find Date of Next Monday:
=CHOOSE(WEEKDAY(A1), A1+1, A1+7,A1+6, A1+5,A1+4,A1+3, A1+2) [Formula]
Formula returns 9/20/2010, date of Next Monday after the Date mentioned in Cell A1 (9/15/2010).
Formula explanation is same as above.
Alternate formula to find date of next Monday:
=CHOOSE(WEEKDAY(A1,2), A1, A1+6, A1+5, A1+4, A1+3, A1+2, A1+1). Note that format of WEEKDAY(A1,2) indicates Number 1 for Monday, ….. till Number 7 for Sunday.
Second Alternate formula to find date of next Monday: =A1+CHOOSE( WEEKDAY(A1), 1,7,6, 5,4,3,2).
Formula to find date of next Sunday: =CHOOSE(WEEKDAY(A1), A1, A1+6, A1+5, A1+4,A1+3, A1+2, A1+1).
Using CHOOSE function in VBA:
Sub chooseEx()
Dim str As String
str = Choose(4, Cells(2, 2), Cells(3, 2), Cells(4, 2), Cells(5, 2), Cells(6, 2))
MsgBox str
End Sub
This vba code returns and displays “Hardy” (for Table 1 format) by passing value to the variable str.