Excel Left Lookup Formula Using VLOOKUP

01
of 03

Find Data to the Left

Excel Left Lookup Formula
Excel Left Lookup Formula. © Ted French

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 left-most 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

  1. Enter the following headings into the cells indicated:
     D1 - Supplier
     E1 - Part
    
  2. Enter the table of data seen in the image above into cells D4 to F9
  3. 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

  1. Click on cell E2 of the worksheet - 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 03

Entering Arguments into the VLOOKUP Dialog Box - Click to View Larger Image

Entering the Arguments for VLOOKUP
Click to View Larger Image. © Ted French

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

  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

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:

  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

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 " , ".

  1. In the VLOOKUP function dialog box, click on the Table_array line
  2. Enter the following CHOOSE function
  3. 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

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

  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. Since we have not yet entered the company name into cell D2, an #N/A error should be present in cell E2
03
of 03

Testing the Left Lookup Formula

Excel Left Lookup Formula
Excel Left Lookup Formula. © Ted French

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

  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
  4. 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.

Format
mla apa chicago
Your Citation
French, Ted. "Excel Left Lookup Formula Using VLOOKUP." ThoughtCo, May. 17, 2017, thoughtco.com/excel-left-lookup-formula-using-vlookup-3124101. French, Ted. (2017, May 17). Excel Left Lookup Formula Using VLOOKUP. Retrieved from https://www.thoughtco.com/excel-left-lookup-formula-using-vlookup-3124101 French, Ted. "Excel Left Lookup Formula Using VLOOKUP." ThoughtCo. https://www.thoughtco.com/excel-left-lookup-formula-using-vlookup-3124101 (accessed January 17, 2018).