Using CHOOSE Function along with VLOOKUP in Excel

In Excel, managing flexible lookups across multiple columns can be challenging when using the VLOOKUP function alone. In such cases, the CHOOSE function helps restructure lookup arrays without rearranging the dataset or adding helper columns. By combining VLOOKUP with CHOOSE, you can dynamically create a lookup table within the formula and perform more advanced lookups efficiently. This approach keeps worksheets cleaner and makes handling complex lookup scenarios easier.

Why Use CHOOSE with VLOOKUP?

• VLOOKUP can only search for a value in the first column of the table array.
• If your lookup value is not in the first column, you would normally need a helper column.
• CHOOSE allows you to reorder columns virtually in memory, so you can bypass this limitation.

We'll begin by briefly reviewing the syntax of the CHOOSE function.

CHOOSE FUNCTION:

Syntax:

=CHOOSE(ARRAY DIMENSIONS(INDEX VALUE(S)),VALUE(1),VALUE(2),VALUE(3),.....)

Here,

1.  Here we will define array dimensions, ie how many columns should the array have.
2. The next parameter(s) will define what value will go in the subsequent array columns, ie VALUE1 will get filled in the first column, and so on.   

Example:

Here we will use this dataset. Let's suppose we have people in a sales firm, and they are evaluated for bonuses. Now, we want to know who did sales for BED SHEETS, by using VLOOKUP and CHOOSE function. So, first of all, let's see what the formula is, and then we will understand it part by part.

=VLOOKUP(B9,CHOOSE({1,2},C2:C6,A2:A6),2,TRUE)

Here the output will be as shown below:

Now, we will see part by part how this formula worked:

•  B9: This is the targeted value to be compared by VLOOKUP to get the result, i.e. BED SHEETS.
• CHOOSE({1,2},C2:C6,A2:A6): This will create an array, having first column values from C2 TO C6,and second column values from A2 TO A6.
• 2: The value will be fetched from this column number.
• TRUE: This parameter is for enabling approximate search, i.e. any keyword found nearly matching with the required word will be given as a result.
• VLOOKUP(B9, CHOOSE({1,2}, C2:C6, A2:A6),1, TRUE): VLOOKUP will search for the desired keyword from the second column of the array, created by CHOOSE function.

Note: The formula above will use an array, so to execute it use Ctrl+Shift+Enter key combination.

The disadvantage of the above function is that VLOOKUP is not capable of doing a case-sensitive search, so to enhance VLOOKUP you have to make it case sensitive. For reference visit this article.