How to Use VLOOKUP with CHOOSE Function in Excel

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 the CHOOSE function in Excel with 5 effective examples. We will also get to know these functions in detail.

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.

Using VLOOKUP with CHOOSE Function in Excel: 5 Effective Examples

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 functions. 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.

In this formula, the 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 type 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.

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 type 2 as the col_num argument for the VLOOKUP function. Lastly, insert TRUE for an approximate match.

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 type 2 as the col_num argument for the VLOOKUP function. Lastly, insert TRUE for an approximate match.

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 Names, and their Obtained Marks as shown in the image below.

Now, let us apply 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, create a table of the index for each category in the 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 wanted 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, multiply 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.

Download Practice Workbook

Download this practice file and try it by yourself.

Use VLOOKUP with CHOOSE in Excel.xlsx

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.

<< Go Back to Excel CHOOSE Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Aaron Mwale Feb 20, 2023 at 5:00 PM

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??
- Reply
  Rafiul Hasan Feb 22, 2023 at 4:34 PM
  
  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