How to Create an Excel Left Lookup Formula Using VLOOKUP

Combine Excel's VLOOKUP and CHOOSE to create a left lookup formula

Microsoft Excel's VLOOKUP function finds and returns information from a table of data based on a lookup value you choose. Typically, 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.

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.

These instructions apply to Excel versions 2019, 2016, 2013, 2010, and Excel for Microsoft 365.

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

  1. Enter the heading Supplier into cell D1.

  2. Enter the heading Part in cell E1.

  3. Enter the table of data seen in the image above into cells D4 to F9.

  4. Rows 2 and 3 are left blank to accommodate the search criteria and the left lookup formula created during this tutorial.

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.

  1. Select cell E2 of the worksheet. E2 is the location where the results of the left lookup formula will be displayed.

  2. Select the Formulas tab of the ribbon.

  3. Select the Lookup & Reference option in the ribbon to open the function drop-down list.

  4. Select VLOOKUP to bring up the function's dialog box.

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.

  1. Select the Lookup_value line in the VLOOKUP dialog box.

  2. Select cell D2 to add this cell reference to the Lookup_value line.

  3. Press the F4 key on the keyboard to make the cell reference absolute: $D$2.

    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.

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.

CHOOSE isn't 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, CHOOSE creates a three-column table.

  1. In the VLOOKUP function dialog box, select the Table_array line.

  2. Enter the following CHOOSE function: 

    CHOOSE({1,2},$F:$F,$D:$D)

    When entering functions manually, separate each of the function's arguments with a comma.

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.

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.

  1. Select the Col_index_num line in the dialog box.

  2. Type a 2 in this line.

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.

  1. Select the Range_lookup line in the dialog box.

  2. Type the word False in this line to indicate that we want VLOOKUP to return an exact match for the data we are seeking.

  3. Select OK to complete the left lookup formula and close the dialog box.

    Because we haven't yet entered the company name into cell D2, an #N/A error appears in cell E2.

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.

  1. Select cell D2 in your worksheet.

  2. Type Gadgets Plus into cell D2 and press Enter or Return.

  3. The text Gadgets, which is the part supplied by the company Gadgets Plus, should be displayed in cell E2.

  4. Test the lookup formula further by typing other company names into cell D2, and the corresponding part name should appear in cell E2.

    If an error message such as #N/A appears in cell E2, check for spelling errors in cell D2. 

Was this page helpful?