This is one of the lookup and reference functions in excel. It is handy to look up your data for data analysis. Mapping data between tables is an essential data table operation. Excel provides a number of helpful functions for managing spreadsheets and processing data.
The Excel CHOOSE function returns a value from the given array based on the index position.
Syntax:
=CHOOSE (index, val1, [val2], …)
Arguments:
index – The position number to choose.
val1 – The first value from which to choose.
val2 – [optional] The second value from which to choose.
The first argument idx in CHOOSE function gives the position of an array.
How to apply CHOOSE function to return a value from an array
Suppose you have data with the Employee_Id field in an excel table and have given a list of employees in an array. You can use CHOOSE function to map.
Please follow the below steps to fill the employee name in column C in your excel
- Go to cell C2
- Type the below formula, it refers cell B2 and returns the respective position name from the employee array
=CHOOSE(B2,"John","Miriam","Ashley")
- Select cell C2 and drag to fill the formula until cell C11
You can see the employee names filled in column C based on the Employee_Id in column B.
How to apply CHOOSE function to return a cell reference from an array
Suppose you have data with the Employee_Id field in an excel table and have given a range of cells in your excel for a list of employee names. You can use CHOOSE function to map employee names by referring to the range of the employee list. In this approach, you refer to the cell instead of the static values.
Please follow the below steps to fill employee name in column C in your excel using cell reference
- Go to cell C2
- Type the below formula, it refers the cell B2 and returns the respective position name from the employee array
=CHOOSE(B2,$G$2,$G$3,$G$4)
- Select cell C2 and drag to fill the formula until cell C11
You can see the employee names filled in column C based on the Employee_Id in column B. This method helps you to change the employee name in column G without altering the CHOOSE function.
Conclusion
You can use CHOOSE function with an array of static employee names or cell references for a list of employee names in your reports or dashboard. CHOOSE function is a type of look-up function.
Use the CHOOSE function instead of the nested IF function, which returns the same results.
