Excel Formulas have benefitted us with solving many complex calculations in large datasets. Among them, **the VLOOKUP function** is widely used for table arrays. On the other hand, **the CHOOSE function** is used to determine the position of any value. In this article, we will learn how to use **VLOOKUP** with** CHOOSE** function in excel with 5 effective examples. We will also get to know these functions in detail.

**Table of Contents**hide

## Download Practice Workbook

Download this practice file and try it by yourself.

## Introduction to Excel VLOOKUP Function

The** VLOOKUP** function is applied to look up data in a table that is organized vertically. It is applicable for both approximate and exact matching. Even it is beneficial for** wildcard characters **(*****, **$**) for partial matches.

**Syntax**

**=VLOOKUP (lookup_value, table_array, column_index_num, [range_lookup])**

**Argument**

** lookup_value: **The value that we need to look for in the first column of the table.

* table_array: *The table from which the value is extracted.

* Column_index_num: *That specific column from which we will retrieve a value.

**Optional Arguments**

** Range_lookup: TRUE** means approximate match (default).

**FALSE**means exact match.

## Introduction to Excel CHOOSE Function

The **CHOOSE** function in Excel returns a value from a list with the help of a given position or index.

**Syntax**

**=CHOOSE (index_num, value1, [value2], …)**

**Argument**

* index_num:* The value to choose a number between

**1**and

**254**.

* value1: *First chosen value

**Optional Arguments**

* value2:* The second value from which we have to choose.

## 5 Effective Examples of Using VLOOKUP with CHOOSE Function in Excel

So far, we got to know both functions in detail. Now, we will use the **VLOOKUP **and **CHOOSE **functions with the following examples.

### Example 1: Combine Excel VLOOKUP & CHOOSE for a Single Condition

In this first example, we will work with **1 condition** to use the **VLOOKUP** and **CHOOSE** function. For this, we prepared a dataset here. It shows a set of **Obtained Marks** relating to the **Obtained Grade** and **Remarks** of an exam.

Now, we will combine both functions for a single condition.

- First, type the number
**67**in**cell C12**as the condition.

- Then, insert this formula in
**cell C13**.

`=VLOOKUP(C12,CHOOSE({1,2},B5:B10,D5:D10),2,TRUE)`

- After that, hit
**Enter**. - That’s it, you will get the look-up value for
**1 condition**.

**VLOOKUP**function is used to look for the value in

**cell C12**. Then,

**CHOOSE({1,2},B5:B10,D5:D10)**is used as the

**table_range**where

**{1,2}**means it will display

**1**or

**2**as index_num argument. Following, typed

**2**as the

**col_num**argument for the

**VLOOKUP**function. Lastly, insert

**TRUE**for an

**approximate match**.

- For proof checking, simply change the value of the condition in
**cell C12**and the output will automatically change like this.

**Read More:** **How to Use CHOOSE Function with Array in Excel**

### Example 2: Apply VLOOKUP with CHOOSE for 2 Conditions

The combination of **VLOOKUP** and **CHOOSE** functions also work in a similar pattern for **2 conditions**. Let’s follow the steps below:

- First, prepare a dataset with the following information.

- Then, type
**Scotland**and**Speaker**as the conditions in**cells C12**and**C13**respectively.

- Afterward, type this formula in
**cell C14**.

`=VLOOKUP(C12&C13,CHOOSE({1,2},B5:B10&C5:C10,D5:D10),2,FALSE)`

- Lastly, press
**Enter**. - Finally, you will see the value is showing for the specified conditions.

Here, the **VLOOKUP** function is used to look for the value in **cells C12 **and **C13**. Then, **CHOOSE({1,2},B5:B10&C5:C10,D5:D10) **is used as the** table_range** where **{1,2} **means it will display **1 **or** 2** as** index_num** argument. Following, typed** 2** as the** col_num** argument for the **VLOOKUP **function. Lastly, insert **TRUE** for an **approximate match**.

**Read More:** **How to Use CHOOSE Function to Perform IF Condition in Excel**

### Example 3: Insert VLOOKUP & CHOOSE for 3 Conditions

Just like 2 conditions, we can also insert the **VLOOKUP** and **CHOOSE **function for **3 conditions**. Go through the steps below:

- First, prepare a dataset in
**cell range B4:E10**with the following information.

- Then, type
**UK**,**Laptop**and**1**as the**3 conditions**.

- Now, apply this formula in
**cell C15**.

`=VLOOKUP(C12&C13&C14,CHOOSE({1,2},B5:B10&C5:C10&D5:D10,E5:E10),2,FALSE)`

- Lastly, hit
**Enter**and you will see the required value.

In the formula above, the **VLOOKUP** function is used to look for the value in **cells C12, C13 **and **C14**. Then, **CHOOSE({1,2},B5:B10&C5:C10,D5:D10,E5:E10) **is used as the** table_range** where **{1,2} **means it will display **1 **or** 2** as** index_num** argument. Following, typed** 2** as the** col_num** argument for the **VLOOKUP **function. Lastly, insert **TRUE** for an **approximate match**.

**Read More:** **Advanced Uses of CHOOSE Function in Excel (9 Applications)**

### Example 4: Excel VLOOKUP for Multiple Criteria

The **VLOOKUP** and **CHOOSE** function is not only confined to conditions, rather it has the versatility to use for **multiple criteria **as well. For this, prepare a dataset with **Student Names**, **Exam Name** and their** Obtained Marks **as shown in the image below.

Now, let us imply the functions for this multiple criteria of the dataset.

- In the beginning, create a table in
**cell range B12:D15**like this.

- Now, insert this formula in
**cell B12**.

`=UNIQUE(B4:B10)`

- Then, press
**Enter**and you will see the student names without any repetition.

Here, **the UNIQUE function **returns the unique value from the** cell range** **B4:B10**.

- Next, apply this formula in
**cell C12**.

`=TRANSPOSE(UNIQUE(C5:C10))`

- After this, hit
**Enter**to separate the unique**Exam Names**.

Here, we combined **the TRANSPOSE** and **UNIQUE** functions to fetch and transpose unique values from the **cell range C5:C10**.

- Lastly, insert this formula in
**cell C13**.

`=VLOOKUP($B13&"|"&C$12,CHOOSE({1,2},$B$5:$B$10&"|"&$C$5:$C$10,$D$5:$D$10),2,0)`

- Next, press
**Enter**>**Autofill**horizontally to get the first set of unique values.

In this formula, **CHOOSE({1,2},$B$5:$B$10&”|”&$C$5:$C$10,$D$5:$D$10) **returns a value from a range of values depending on the index number. Then,** 2** indicates the** col_index_num** argument. Lastly, **0** represents the **[range_lookup]** argument.

- Finally, use the
**AutoFill**tool and you will get all outputs like this.

### Example 5: Use VLOOKUP with CHOOSE for Multiple Tables

In this last example, let us consider two tables with **Category A** and **Category B** that have a discount rate according to quantity. Along with it, also create a table of index for each category in **cell range H4:I6**.

Now, we will use the **VLOOKUP** and **CHOOSE** functions to find the discount price of this dataset as shown below.

- In the beginning, insert this formula in
**cell E12**to get the category of the first product.

`=MID(B12,2,1)`

- Then, press
**Enter**and**Autofill**up to**cell E17**to get all the categories.

Here, we used **the MID function** to get a specific character from **cell B12**. For this reason, we specified that we want to extract the second character and therefore, typed **2**. Also, we want only one character and therefore, type **1** in the end.

- Now, put this formula in
**cell F12**to get the total price of the first product.

`=C12*D12`

- Following, press
**Enter**>**Autofill**to get all the total prices.

- After this, select the first table for
**Category A**in**cell range B4:C9**. - Then, go to the
**Home**tab and click on**Format as Table**under the**Styles**group.

- Next, select any type of table from the drop-down menu.

- Following, change the
**Table Name**in the**Table Design**tab.

- Similarly, follow this process for
**Category B**and**Index**table as well. - Finally, we have come to our last step.
- At this point, insert this formula in
**cell G12**.

`=CHOOSE(VLOOKUP(E12,Index,2,FALSE),VLOOKUP(D12,CatA,2),VLOOKUP(D12,CatB,2))*F12`

- Finally, press
**Enter**>**Autofill**to get all**Discount**amounts in**cell range G12:G17**.

In this formula, **VLOOKUP(E12,Index,2,FALSE) **looks up the value in **cell E12** based on the **Index** table with reference from **column 2 **in the dataset. Then typed** FALSE** for an **exact match**. Following, we inserted **VLOOKUP(D12,CatA,2) **and **VLOOKUP(D12,CatB,2)) **to find the value in both category tables. Lastly, multiplied this whole formula by the total amount in cell **F12**.

- If you want to check whether the result is correct or not for the conditions, simply multiply the total price of
**cell F12**by**9%**according to the information in the**Category A**table and put this formula in**cell H12**.

`=F12*9%`

- Press
**Enter**and you will see that the result matches the**Discount**price that we got using the**VLOOKUP**and**CHOOSE**functions.

**Read More:** **How to Apply CHOOSE Function to Create Drop-Down List in Excel**

## Conclusion

I hope it was a helpful article for you on how to use **VLOOKUP** with **CHOOSE **function in Excel with 5 effective examples. Let us know your suggestions in the comment box. Follow **ExcelDemy** for more tutorials.

I use Excel 365. Sometimes excel hangs and it will start the undo and I cannot stop it. What causes this and how can I stop it??

Hello AARON MWALE,

Thanks for your comment. Sorry to hear that you’re facing issues with Excel.

When Excel hangs and starts an Undo action, that means Excel is trying to process a large amount of data or executing a complex operation. This may cause the program to be unresponsive for a period of time while it completes the task.

In order to stop the undo action, you can try pressing the “Esc” key on your keyboard. If this does not work, you can try pressing “Ctrl” & “Break” on your keyboard. If don’t find these helpful, you may need to wait for Excel to finish the operation before you can regain control of the program.

To prevent Excel from hanging and starting an Undo action in the future, you can try the following:

1. Limit the amount of data you are working with at one time. If you are working with a large amount of data, try breaking it up into smaller chunks that are easier for Excel to handle.

2. Close any unnecessary programs or applications running in the background. This can free up system resources and improve Excel’s performance.

3. Disable any add-ins or macros that may be causing Excel to slow down or become unresponsive.

4. Check for and install any available updates for Excel. Updates often include bug fixes and performance improvements that can help prevent Excel from hanging in the future.

5. Consider upgrading your computer hardware if it is outdated or underpowered. Excel can be resource-intensive, and having a fast and capable computer can make a big difference in its performance.

Hope you find these ways helpful to overcome your issues.

Regards,

Rafi

ExcelDemy team