Excel CHOOSE Function with Array
Last Updated :
19 Sep, 2023
We often use the CHOOSE function in Excel to pick something from a list. In this article, we’ll see how to use CHOOSE with a bunch of things (we call it an array) to do various stuff in Excel. This array can be either many cells we put into the function or many things we get from the function.
What is CHOOSE Function with Array in Excel
The Excel CHOOSE function stands out as a powerful tool that enables you to retrieve the values or references from an array based on a specified position. This guide retrieves values or references from an array based on a specified position. In this context, “array” refers to the scenario where we either provide multiple cells as input within the function or obtain multiple results by using the function.
Syntax of CHOOSE Function
=CHOOSE (index_num, value1, [value2], …)
Arguments:
- index_num: The value to choose.
- value1: The first value from which to choose.
- value2 [optional]: The second value from which to choose.
- index_num: It is the first argument for the CHOOSE function and it refers to the position of an array.
Sample Data: In the Sales Table, we have given Products_Id. We use Excel CHOOSE() to fill in the Actual product name in the next column (Column – D).

How to Use CHOOSE Function with Array in Excel
You can learn how to use CHOOSE Function with Array in Excel by the following methods:
- Return Single Value in Excel Using CHOOSE Function with Array
- Apply CHOOSE Function with Array to Return Multiple Values in Excel
- Assign CHOOSE Function with Array to Retuen a Cell Reference
- Apply CHOOSE Function with Array for Left VLOOKUP
How to Return Single Value in Excel Using CHOOSE Function With Array
Step 1: Type Products in Cell D1
Type “Products” in cell D1
Step 2: Use CHOOSE Function
Write a CHOOSE function in cell D2
=CHOOSE(C2,”Fashion”,”Electronics”,”Daily Essentials”)

Step 3: Drag the Formula
Select cell D2 and drag till cell D11

How to Apply CHOOSE Function with Array to Return Multiple Values in Excel
We have a Dataset (A1:D11) in Excel Containing the Products with their ID. We need to pick a ID along with the products_ID and Product Name. Here we will use the CHOOSE Function in Excel with the array to return these multiples values. By applying the formula of this method we can get the values in a row even if they are in a column.
Step 1: Select the Cell
Select the cell where you need the result.
.png)
Step 2: Enter the Formula in the Cell
Enter the Formula in the Cell “=CHOOSE({1,2,3},
.png)
Step 3: Preview the Result
Drag the Formula to all the cells and see the Result.
.png)
How to Apply CHOOSE Function with Array to Returning a cell reference
Step 1: Type “Products” in cell D1
Step 2: Create a Product table (unique list of Products)

Step 3: Write a CHOOSE function in cell D2
=CHOOSE(C2, $H$2,$H$3,$H$4)

Step 4: Select cell D2 and drag till cell D11

How to Use CHOOSE Function with Array for Left VLOOKUP
When we want to find something in Excel using the VLOOKUP function, it typically only searches in the leftmost column. However, sometimes we need to look in other columns. We can solve this by using the CHOOSE function together with an array.
Here’s a simple example to understand this:
Step 1: Enter the Data Set
We have a dataset that lists product names and their corresponding IDs (in cells B2:D11).
.png)
Step 2: Search for the Value
Let’s say we want to find the product of the Product_ID “418” and place it in cell F5.
Step 3: Enter the VLOOKUP Formula
First, we try using VLOOKUP alone, but it has a limitation as it only searches the leftmost column.
.png)
Step 4: Use VLOOKUP and CHOOSE Function
Then, we’ll show you how to overcome this limitation by combining the CHOOSE function with VLOOKUP.
This combination allows us to search for information in columns other than the leftmost one.
Formula :=VLOOKUP(F4,CHOOSE({1,2},A4:A8,C4:C8),2,FALSE)
.png)
Similar Reads
How to use CHOOSE Function in Excel
The CHOOSE function is technically part of Excelâs lookup function and can be incredibly useful. The CHOOSE function returns a value from a list using an index. One of those Excel features, CHOOSE, may not seem helpful alone, but when paired with other functions, it offers a ton of fantastic advanta
7 min read
Excel IF function With Examples
The IF function in Excel is one of the most powerful and commonly used formulas that allows you to perform logical tests and return different values based on whether the condition is true or false. If youâve ever needed to check whether a value meets certain criteria, then the IF function is the too
12 min read
Using CHOOSE Function along with VLOOKUP in Excel
In Excel, managing space is a very important point to consider. Now, let's suppose you are in a situation where you want to compare multiple columns in the Excel dataset, and you want to derive results, This is not possible using the VLOOKUP function alone. So, to make a function capable of doing so
2 min read
Excel VBA | Average() Functions
VBA (Visual Basic for Applications) is the programming language of Excel and other offices. It is an event-driven programming language from Microsoft. With Excel VBA, one can automate many tasks in excel and all other office softwares. It helps in generating reports, preparing various charts, graphs
2 min read
Excel ROWS and COLUMNS Functions with Examples
In Microsoft Excel, where precision and efficiency reign supreme, mastering the art of maneuvering through cells and worksheets is indispensable. Two indispensable components in your Excel toolbox are the ROW and COLUMN functions, each meticulously crafted to execute discrete functions that can subs
7 min read
Top Excel Data Analysis Functions
Have you ever analyzed any data? What does it mean? Well, analyzing any kind of data means interpreting, collecting, transforming, cleaning, and visualizing data to discover valuable insights that drive smarter and more effective decisions related to business or anywhere you need it. So, Excel solve
6 min read
Excel MID Function
If you use programming languages, like Python, you must have used a function called string slicing, which extracts a part of the string and can directly print it, or store a result. The same functionality can be achieved in Excel when we use Excel's MID function. The MID function can return a part o
7 min read
How to Combine Excel Functions in a Formula?
We all use Excel for some small computations in data but excel also provides the functionality to combine these small functions and make a formula for manipulating the data. For example, let's say that in school we need to increment the ID numbers of students by a number but the ID's have some text
2 min read
SUMIF Function in Excel with Examples
The SUMIF function in Excel is a versatile tool that enables you to sum values based on specific criteria, such as a true or false condition. This feature makes it perfect for conditional summation tasks like totaling sales over a particular amount, adding up expenses in specific categories, or summ
7 min read
OFFSET Function in Excel With Examples
Excel contains many useful formulas and functions that make it more and more useful and at the same time user-friendly. Such a function is the OFFSET() function. In many cases, this function is also used inside another function. This function basically returns a reference of a single cell or a range
4 min read