“CHOOSE”
Used for – “CHOOSE” function in MS Excel
is used to find the relative position of the value within a range.
Syntax – CHOOSE(index_num, value1,
[value2], …)
index_num – It is a whole number in
between 1 to 254 which either you typed or calculated as a result of formula/reference
cell which needs to tell MS Excel which relative position it has to pick out of
given set of values.
value1 – This
is a result you want to get when index_num ends up with 1. It can be a
text/number/reference cell/ a formula. It is compulsory field without which
formula results in #N/A error.
[value2] – This
is an optional field. Likewise value1 it is also the result of the function
choose when index_num ends up with 2. You can add upto 254 values in this
function.
Example – Following example will clear the CHOOSE function:-
In below table column A, B, & C
contains some sales data for particular item in specified cities. Now column G
& H contains the CHOOSE function with different criteria.
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
|
1
|
S. No.
|
City
|
ITEM
|
Sale
|
Qty.
|
Choose Formula
|
||
2
|
1
|
New Delhi
|
Orange
|
1000
|
1500
|
Sale at S.No.1
|
=CHOOSE(1,D2,D3,D4,D5,D6,D7)
|
|
3
|
2
|
New Delhi
|
Banana
|
2000
|
1250
|
1
|
=CHOOSE(G3,D2,D3,D4,D5,D6,D7)
|
|
4
|
3
|
Muktsar
|
Papaya
|
3000
|
1000
|
5
|
=CHOOSE(G4,D2,D3,D4,D5,D6,D7)
|
|
5
|
4
|
Muktsar
|
Orange
|
4000
|
750
|
|||
6
|
5
|
Chandigarh
|
Banana
|
5000
|
500
|
|||
7
|
6
|
Chandigarh
|
Papaya
|
6000
|
250
|
And
result you get as under:-
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
I
|
|
1
|
S. No.
|
City
|
ITEM
|
Sale
|
Qty.
|
||||
2
|
1
|
New Delhi
|
Orange
|
1000
|
1500
|
Sale at S.No.1
|
1000
|
||
3
|
2
|
New Delhi
|
Banana
|
2000
|
1250
|
1
|
1000
|
||
4
|
3
|
Muktsar
|
Papaya
|
3000
|
1000
|
5
|
5000
|
||
5
|
4
|
Muktsar
|
Orange
|
4000
|
750
|
||||
6
|
5
|
Chandigarh
|
Banana
|
5000
|
500
|
||||
7
|
6
|
Chandigarh
|
Papaya
|
6000
|
250
|
NOTE :-
Please
do care of following points when you use CHOOSE :-
- In any case the index_num has a value greater than 254 the function ends up with #VALUE! error.
- If the values are less than index_num then also the function ends up with #VALUE! error.
- It doesnot allow to enter values more than 254.
- If index_num is an array, every value is evaluated when CHOOSE is evaluated
Dear readers, after reading the Content please ask for advice and to provide constructive feedback Please Write Relevant Comment with Polite Language.Your comments inspired me to continue blogging. Your opinion much more valuable to me. Thank you.