How to Create an Excel Left Lookup Formula Using VLOOKUP

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

Excel's VLOOKUP function is used to find and return information from a table of data based on a lookup value that 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, by combining VLOOKUP with the CHOOSE function, a left lookup formula can be created that:

  • Permits the lookup value to be from any column in the data table
  • Returns information located in any column to the left of the lookup value

Note: These instructions apply to Excel versions 2019, 2016, 2013, 2010, and Excel for Office 365.

01
of 05

Tutorial: Using the VLOOKUP and CHOOSE Functions in a Left Lookup Formula

Excel Left Lookup Formula

Ted French

To create the left lookup formula seen in the example image, use the formula:

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

In this example, the formula 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 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.

Enter the Tutorial 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 is 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 is 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. Click on cell E2 of the worksheet. E2 is the location where the results of the left lookup formula will be displayed.
  2. Click on the Formulas tab of the ribbon.
  3. Click on the Lookup & Reference option in the ribbon to open the function drop-down list.
  4. Click on VLOOKUP in the list to bring up the function's dialog box.
02
of 05

Entering Arguments Into the VLOOKUP Dialog Box

Entering the Arguments for VLOOKUP

Ted French

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.

Enter the following values for each of VLOOKUP's arguments on the correct line of the dialog box as shown in the accompanying image.

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.

  1. Click on the lookup_value line in the dialog box.
  2. Click on 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

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

The table array argument is the block of contiguous data from which specific information is retrieved.

Usually, VLOOKUP only looks 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 using the CHOOSE function.

In this formula, the CHOOSE function accomplishes two tasks:

  1. It creates a table array that is only two columns wide (columns D and F).
  2. 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.

Entering Functions

When entering functions manually, each of the function's arguments must be separated by a comma.

  1. In the VLOOKUP function dialog box, click on the Table_array line.
  2. Enter the following CHOOSE function: CHOOSE({1,2},$F:$F,$D:$D)

Column Index Number

Normally, the column index number indicates which column of the table array contains the data you are 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. Click on 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 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 only uses 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 is set to False so that only exact matches are returned by the formula.

  1. Click on 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. Click OK to complete the left lookup formula and close dialog box.
  4. Because we have not yet entered the company name into cell D2, an #N/A error appears in cell E2.
03
of 05

Returning Data With the Left Lookup Formula

Excel Left Lookup Formula

Ted French

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. Click on cell D2 in your worksheet.
  2. Type Gadgets Plus into cell D2 and press the ENTER key on the keyboard.
  3. 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.

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

04
of 05

Creating a Two-Column Table Array

The syntax for the CHOOSE function is:

= CHOOSE (Index_number, Value1, Value2, ...Value254)

The CHOOSE function typically 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.

When multiple index numbers are entered, the function returns multiple values in any order desired. Getting CHOOSE to return multiple values is done by creating an array.

To enter an array, surround 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, CHOOSE creates a three-column table.

Additional columns allow you to return different information with the left lookup formula by changing VLOOKUP's column index number argument to the number of the column containing the desired information.

05
of 05

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 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 they supply.