However, combining VLOOKUP with the CHOOSE function creates a left lookup formula where the lookup value can be from any column in the data table. The formula returns information located in any column to the left of the lookup value.
Create a Left Lookup Formula
In this example, we’ll create a left lookup formula to find the part supplied by the different companies listed in column 3 of a data table. In this formula, the CHOOSE function will trick VLOOKUP into believing that column 3 is column 1. As a result, we can use the company’s name as the lookup value to find the name of the part supplied by each company.
Entering the Data
Open the VLOOKUP Dialog Box
Although it’s possible to type the formula directly into cell F1 in the worksheet, many people have difficulty with the syntax of the formula. In this case, it’s safer to use the VLOOKUP dialog box. Almost all of Excel’s functions have a dialog box that allows you to enter each of the function’s arguments on a separate line.
Enter Arguments in the VLOOKUP Box
A function’s arguments are the values used by the function to calculate a result. In a function’s dialog box, the name of each argument is on a separate line followed by a field in which to enter a value. Here’s what you’ll enter in each section of the VLOOKUP Dialog box.
Lookup Value
The lookup value is the field of information used to search the table array. VLOOKUP returns another field of data from the same row as the lookup value. This example uses a cell reference to the location where the company name will be entered into the worksheet. This makes it easy to change the company name without editing the formula.
Entering the CHOOSE Function Into the Table Array
The table array argument is the block of contiguous data from which specific information is retrieved. Usually, VLOOKUP looks only to the right of the lookup value argument to find data in the table array. To get it to look left, VLOOKUP must be tricked by rearranging the columns in the table array. We do this by using the CHOOSE function. In this formula, the CHOOSE function creates a table array that is only two columns wide (columns D and F), and it changes the right-to-left order of the columns in the table array so that column F comes first and column D is second. Since the CHOOSE function sets VLOOKUP’s table array (the source of data for that function), switching the order of the columns in the CHOOSE function is passed along to VLOOKUP. As far as VLOOKUP is concerned, the table array is only two columns wide with column F on the left and column D on the right. Since column F contains the name of the company we want to search for, and since column D contains the part names, VLOOKUP can perform its regular lookup duties in finding data that is located to the left of the lookup value. As a result, VLOOKUP can use the company name to find the part it supplies.
Column Index Number
Normally, the column index number indicates which column of the table array contains the data you’re after. However, in this formula, it refers to the order of columns set by the CHOOSE function. CHOOSE({1,2},$F:$F,$D:$D) The CHOOSE function creates a table array that is two columns wide with column F first followed by column D. Since the information sought (the part name) is in column D, the value of the column index argument must be set to 2.
Range Lookup
VLOOKUP’s Range_lookup argument is a logical value (TRUE or FALSE only) that indicates whether you want VLOOKUP to find an exact or an approximate match to the lookup value. If TRUE or if this argument is omitted, VLOOKUP returns either an exact match to the Lookup_value or, if an exact match isn’t found, VLOOKUP returns the next largest value. For the formula to do this, the data in the first column of Table_array must be sorted in ascending order. If FALSE, VLOOKUP uses only an exact match to the Lookup_value. If there are two or more values in the first column of the Table_array that match the lookup value, the first value found is used. If an exact match isn’t found, a #N/A error is returned. Since we’re looking for a particular part name, we’ll set Range_lookup to False so that the formula returns only exact matches.
Testing the Left Lookup Formula
To find which companies supply which parts, type a company’s name into cell D2 and press the ENTER key on the keyboard. The part name is displayed in cell E2.
