Extending VLOOKUP with CHOOSE

One of the more repetitive tasks an Analyst gets stuck with on a daily basis is looking up values using more than one criterion. This can be fixed by creating a temporary “Combination” column or by using several flavours of formula.

For example if we wanted to look up the Cost of Sales against a sales person, except the only way to properly identify the sales person is by looking up the Cost against the firstname and surname columns.

The simplest remedy to this problem is to create an extra column that is a combination of both the first and last names of the person, thereby creating a new lookup column. This can be achieve by:

  • Inserting a new column at the front of the range data that you are searching; this is your new identifier column.Conditional Vlookup
  • The identifier column should consist of more than one column and should uniquely identify the data you are after. In this case it is a combination of the first and last name of the salesperson.Unique Identifier
  • The vlookup statement is now modified to use the new identifier columnConditional Vlookup

Alternatively you could use vlookup with choose. Choose returns an nth value of an array and has the following syntax:

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

Ordinarily choose will return one value from a list of values. For example:

=CHOOSE(4,”TESTING”,”THIS”,”FUNCTION”,”FOR”,”YOU) will return FOR, however if you provide multiple index numbers the function will return multiple values. This will change the syntax of the statement slightly. It will also return a range that can be subbed into your VLOOKUP formula.

=CHOOSE({1,2}, x,x)

Two indexes mean that the function will now create a two column table. Similarly if you wanted to create a three column table your syntax would look like this:

=CHOOSE({1,2,3},… ,… ,…)

To create our temporary search column in memory, part of the choose statement will need to be an array, because it is the identifying part of our vlookup statement, the array part will need to be the first value of the choose statement.

=CHOOSE({1,2}, B11:B16 & C11:C16, …)

The second column will be the value which will be returned after the correct row has been identified, which in this case is the cost which ranges from D11 to D16. So the formula for Choose will now look like this:

=CHOOSE({1,2},B11:B16 & C11:C16,D11:D16)

This will retrieve the below screenshot as a range (which will actually only output an “#N/A” in a cell).

After the range is subbed into your vlookup formula

=VLOOKUP(A4&B4,CHOOSE({1,2},B11:B16 & C11:C16,D11:D16),2,0)

Choose Vlookup Conditional

Hit Ctrl+Shift and Enter. This will place {} braces around the formula and indicate to excel that this is an array formula.

{=VLOOKUP(A4&B4,CHOOSE({1,2},B11:B16 & C11:C16, D11:D16),2,0)}

The only reason the array formula (B11:B16 & C11:C16) is required in this circumstance is because a resultant column needed to be created from Columns B and C.

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.