CHOOSE Function in excel(Table of Contents)
Excel CHOOSE Function
Choose function in excel simply returns a value from the selected list or array from any specific position. In a simple language, we choose function returns a value based on the given position from the selected available list of values. This function can be used in Excel worksheets or as VBA functions as well.
Formula:
Below is the CHOOSE Formula in Excel :
The CHOOSE formula has the following arguments:
- Index_num = The position of a value for which we are looking for. It will always be a number between 1 and 254.
- Value 1 = The first value/list from which to choose.
- Value 2[optional] = The second value/list from which to choose.
In choose function, Values parameter can be the cell references or the cell range. If Index_num is 1, then it will return Value 1.
Explanation of CHOOSE Function in excel
When we are looking for a value corresponding to the index number, or we are applying Vlookup function and then we realized that we have to pick the data from the left side. As Vlookup function works on the right side of the lookup value.
These problems can be solved by using the CHOOSE function in excel. Here we will discuss some examples for solving the above problems.
How to Use CHOOSE Function in Excel?
Let’s take a few CHOOSE function in excel examples, before using the Choose function workbook:
Example #1
Let suppose, We have ranks from 1 to 4 and fruits Orange, Apple, Water Melon and Banana. Now in the result, we want for rank 1, it should be Orange, rank 2 it should be Apple and so on.
For this problem, we will use the choose function.
- Click on Formulas tab.
- Then click on lookup and reference and select CHOOSE Function.
- In cell B3, we wrote =choose then bracket open and click on insert function. It will open a function arguments dialog box as per the below screenshot.
- In Index_num – select A3 cell for which we are looking value.
- In Value 1 – Pass First list value ie Fruit name Orange. It automatically takes the parameter as text in double quotes.
- In Value 2 – Pass Second fruit name Apple.
- In Value 3 – Pass Third fruit name Water Melon.
- In Value 4 – Pass Fourth fruit name Banana.
- Click on ok.
It will give the below result:
Drag & drop the value from B3 to B6 and it will produce the below results:
Example #2
Suppose you have below list and you want to pick value to exist on 3rd position from the below list:
Now, you can use the formula =CHOOSE(3, A11, A12, A13, A14, A15, A16, A17)
The result is 43.
Example #3
We also can pass the range in place of values. Please see below example:
=CHOOSE(3, A22: A24, B22: B24, C22: C24) – we have taken the first argument as 3 because we want to see the total of salary which is the 3rd column in the above data. Above data is a company employee data. Now we want to see the sum of the salary given by the company. Here we will use choose function with the sum function.
3rd index_num will choose the value from the 3rd range. If we pass 2nd as index_num then it will choose the value from the 2nd range.
Example #4
We are taking an example of an XYZ company employee.
In the above example, we want to pick the Emp_id corresponding to the Emp_name. Here we will apply Vlookup function along with CHOOSE function. Syntax is:
=VLOOKUP(G30, CHOOSE({1, 2,3, 4}, $B$30:$B$35, $C$30:$C$35, $D$30:$D$35, $A$30:$A$35), 4, FALSE)
Here we are passing an array at the place of the first argument in CHOOSE function. Index 1 is indicating Column B, Index 2 is indicating Column C, Index 3 is indicating Column D and Index 4 is indicating Column A.
Result is :
This method is also called Left lookup formula with Lookup Function.
Recommended Articles
This has been a guide to CHOOSE function in excel. Here we discuss the CHOOSE Formula in excel and how to use CHOOSE function in excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
