Find Data to the Left
Excel Left Lookup Formula Overview
Excel's VLOOKUP function is used to find and return information from a table of data based on a lookup value that you choose.
Normally, VLOOKUP requires the lookup value to be in the leftmost column of the table of data, and the function returns another field of data located in the same row to the right of this value.
By combining VLOOKUP with the CHOOSE function; however, a left lookup formula can be created that will:
 permits the lookup value to be chosen from any column in the data table
 return information located in any column to the left of the lookup value
Example: Using the VLOOKUP and CHOOSE Functions in a Left Lookup Formula
The steps detailed below create the left lookup formula seen in the image above.
The formula
=VLOOKUP($D$2,CHOOSE({1,2},$F:$F,$D:$D),2,FALSE)
makes it possible to find the part supplied by the different companies listed in column 3 of the data table.
The job of the CHOOSE function in the formula is to trick VLOOKUP into believing that column 3 is actually column 1. As a result, the Company's name can be used as the lookup value to find the name of the part supplied by each company.
Tutorial Steps  Entering the Tutorial Data
 Enter the following headings into the cells indicated:
D1  Supplier E1  Part
 Enter the table of data seen in the image above into cells D4 to F9
 Rows 2 and 3 are left blank in order to accommodate the search criteria and the left lookup formula created during this tutorial
Starting the Left Lookup Formula  Opening the VLOOKUP Dialog Box
Although it is possible to just type the formula above directly into cell F1 in the worksheet, many people have difficulty with the syntax of the formula.
An alternative, in this case, is 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.
Tutorial Steps
 Click on cell E2 of the worksheet  the location where the results of the left lookup formula will be displayed
 Click on the Formulas tab of the ribbon
 Click on the Lookup & Reference option in the ribbon to open the function drop down list
 Click on VLOOKUP in the list to bring up the function's dialog box
Entering Arguments into the VLOOKUP Dialog Box  Click to View Larger Image
VLOOKUP's Arguments
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 located on a separate line followed by a field in which to enter a value.
Enter the following values for each of VLOOKUP's arguments on the correct line of the dialog box as shown in the image above.
The Lookup Value
The lookup value is the field of information that is 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. The advantage of this is that it makes it easy to change the company name without editing the formula.
Tutorial Steps
 Click on the lookup_value line in the dialog box
 Click on cell D2 to add this cell reference to the lookup_value line
 Press the F4 key on the keyboard to make the cell reference absolute  $D$2
Note: Absolute cell references are used for the lookup value and table array arguments to prevent errors if the lookup formula is copied to other cells in the worksheet.
The Table Array: Entering the CHOOSE Function
The table array argument is the block of contiguous data from which specific information is retrieved.
Normally, VLOOKUP only looks to 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 using the CHOOSE function.
In this formula, the CHOOSE function accomplishes two tasks:
 it creates a table array that is only two columns wide  columns D and F
 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
Details of how the CHOOSE function accomplishes these tasks can be found on page 3 of the tutorial.
Tutorial Steps
Note: When entering functions manually, each of the function's arguments must be separated by a comma " , ".
 In the VLOOKUP function dialog box, click on the Table_array line
 Enter the following CHOOSE function

CHOOSE({1,2},$F:$F,$D:$D)
The Column Index Number
Normally, the column index number indicates which column of the table array contains the data you are after. In this formula; however, it refers to the order of columns set by the CHOOSE function.
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.
Tutorial Steps
 Click on the Col_index_num line in the dialog box
 Type a 2 in this line
The 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 is not 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 will only use an exact match to the Lookup_value. If there are two or more values in the first column of Table_array that match the lookup value, the first value found is used. If an exact match is not found, a #N/A error is returned.
In this tutorial, since we are looking for a particular part name, Range_lookup will be set to False so that only exact matches are returned by the formula.
Tutorial Steps
 Click on the Range_lookup line in the dialog box
 Type the word False in this line to indicate that we want VLOOKUP to return an exact match for the data we are seeking
 Click OK to complete the left lookup formula and close dialog box
 Since we have not yet entered the company name into cell D2, an #N/A error should be present in cell E2
Testing the Left Lookup Formula
Returning Data with 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 will be displayed in cell E2.
Tutorial Steps
 Click on cell D2 in your worksheet
 Type Gadgets Plus into cell D2 and press the ENTER key on the keyboard
 The text Gadgets  the part supplied by the company Gadgets Plus  should be displayed in cell E2
 Test the lookup formula further by typing other company names into cell D2 and the corresponding part name should appear in cell E2
VLOOKUP Error Messages
If an error message such as #N/A appears in cell E2, first check for spelling errors in cell D2.
If spelling is not the problem, this list of VLOOKUP error messages may help you determine where the problem lies.
Breaking Down the CHOOSE Function's Job
As mentioned, in this formula, the CHOOSE function has two jobs:
 it creates a table array that is only two columns wide  columns D and F
 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
Creating a Two Column Table Array
The syntax for the CHOOSE function is:
= CHOOSE (Index_number, Value1, Value2, ...Value254)
The CHOOSE function normally returns one value from the list of values (Value1 to Value254) based on the index number entered.
If the index number is 1, the function returns Value1 from the list; if the index number is 2, the function returns Value2 from the list and so on.
By entering multiple index numbers; however, the function will return multiple values in any order desired. Getting CHOOSE to return multiple values is done by creating an array.
Entering an array is accomplished by surrounding the numbers entered with curly braces or brackets. Two numbers are entered for the index number: { 1,2 }.
It should be noted that CHOOSE is not limited to creating a two column table. By including an additional number in the array  such as { 1,2,3 }  and an additional range in the value argument, a three column table can be created.
Additional columns would allow you to return different information with the left lookup formula simply by changing VLOOKUP's column index number argument to the number of the column containing the desired information.
Changing the Order of Columns with the CHOOSE Function
In the CHOOSE function used in this formula: CHOOSE({1,2},$F:$F,$D:$D), the range for column F is listed before column D.
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 gets passed along to VLOOKUP.
Now, 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 will be able to perform its normal lookup duties in finding data that is located to the left of the lookup value.
As a result, VLOOKUP is able to use the company name to find the part they supply.