Quickly Find Multiple Fields of Data with Excel's VLOOKUP Function

Directly Above Shot Of Magnifying Glass On Table
Chee Siong Teh / EyeEm / Getty Images

By combining Excel's VLOOKUP function with the COLUMN function we can create a lookup formula that allows you to return multiple values from a single row of a database or table of data.

of 10

Return Multiple Values with Excel VLOOKUP

Return Multiple Values with Excel VLOOKUP

Ted French

Following the steps listed below creates the lookup formula seen in the image above that will return multiple values from a single data record.

The lookup formula requires that the COLUMN function is nested inside of VLOOKUP. Nesting a function involves entering the second function as one of the arguments for the first function.

In this tutorial, the COLUMN function will be entered as the column index number argument for VLOOKUP. The last step in the tutorial involves copying the lookup formula to additional columns in order to retrieve additional values for the chosen part.

of 10

Enter the Tutorial Data

Entering the Tutorial Data

 Ted French

The first step in the tutorial is to enter the data into an Excel worksheet. In order to follow the steps in the tutorial enter the data shown in the image above into the following cells:

  • Enter the top range of data into cells D1 to G1.
  • Enter the second range into cells D4 to G10.

The search criteria and the lookup formula created during this tutorial will be entered into row 2 of the worksheet.

The tutorial does not include the formatting seen in the image, but this will not affect how the lookup formula works. Information on formatting options similar to those seen above is available in this Basic Excel Formatting Tutorial.

of 10

Creating a Named Range for the Data Table

Creating a Named Range in Excel

Ted French

A named range is an easy way to refer to a range of data in a formula. Rather than typing in the cell references for the data, you can just type the name of the range.

A second advantage for using a named range is that the cell references for this range never change even when the formula is copied to other cells in the worksheet. Range names are, therefore, an alternative to using​ absolute cell references to prevent errors when copying formulas.

The range name does not include the headings or field names for the data (row 4) but only the data itself.

  1. Highlight cells D5 to G10 in the worksheet to select them.
  2. Click on the Name Box located above column A.
  3. Type Table into the Name Box.
  4. Press the Enter key on the keyboard.
  5. Cells D5 to G10 now have the range name of Table. We will use the name for the VLOOKUP table array argument later in the tutorial.
of 10

Opening the VLOOKUP Dialog Box

Opening the VLOOKUP Dialog Box

Ted French

Although it is possible to just type our lookup formula directly into a cell in a worksheet, many people find it difficult to keep the syntax straight — especially for a complex formula such as the one we are using in this tutorial.

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 two-dimensional 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.
  4. Click on VLOOKUP in the list to open the function's dialog box.
of 10

Entering the Lookup Value Argument

Entering the Lookup Value Argument

Ted French

Normally, the lookup value matches a field of data in the first column of the data table. In our example, the lookup value refers to the name of the hardware part about which we want to find information. The allowable types of data for the lookup value are text data, logical values, numbers, and cell references.

Absolute Cell References

Normally, when formulas are copied in Excel, cell references change to reflect their new location. If this happens, D2, the cell reference for the lookup value, will change as the formula is copied creating errors in cells F2 and G2.

Absolute cell references do not change when formulas are copied.

To prevent the errors, we will convert the cell reference D2 into an absolute cell reference. Absolute cell references are created by pressing the F4 key on the keyboard. Doing so adds dollar signs around the cell reference such as $D$2.

  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 — this is the cell where we will type the part name about which we are seeking information
  3. Without moving the insertion point, press the F4 key on the keyboard to convert D2 into the absolute cell reference $D$2.
  4. Leave the VLOOKUP function dialog box open for the next step in the tutorial.
of 10

Entering the Table Array Argument

Entering the Table Array Argument

Ted French

The table array is the table of data that the lookup formula searches to find the information we want. The table array must contain at least two columns of data.

The first column contains the lookup value argument (previous step in the tutorial), while the second column will be searched by the lookup formula to find the information we specify.

The table array argument must be entered as either a range containing the cell references for the data table or as a range name.

  1. Click on the table_array line in the dialog box.
  2. Type Table to enter the range name for this argument.
  3. Leave the VLOOKUP function dialog box open for the next step in the tutorial.
of 10

Nesting the COLUMN Function

Nesting the COLUMN Function in VLOOKUP

 Ted French

Normally VLOOKUP only returns data from one column of a data table and this column is set by the column index number argument.

In this example, however, we have three columns that we wish to return data from so we need a way to easily change the column index number without editing our lookup formula — this is where the COLUMN function comes in.

By entering it as the column index number argument, it will change as the lookup formula is copied from cell D2 to cells E2 and F2 later on in the tutorial.

Nesting Functions

The COLUMN function, therefore, acts as VLOOKUP's column index number argument; this is accomplished by nesting the COLUMN function inside of VLOOKUP in the Col_index_num line of the dialog box.

Entering the COLUMN Function Manually

When nesting functions, Excel doesn't allow us to open the second function's dialog box to enter its arguments. The COLUMN function, therefore, must be entered manually in the Col_index_num line. The COLUMN function has only one argument — the Reference argument which is a cell reference.

Choosing the COLUMN Function's Reference Argument

The COLUMN function's job is to return the number of the column given as the Reference argument. In other words, it converts the column letter into a number with column A being the first column, column B the second and so on.

Since the first field of data, what we want returned is the price of the item, which is in column two of the data table. We can choose the cell reference for any cell in column B as the Reference Argument in order to get the number 2 for the Col_index_num argument.

  1. In the VLOOKUP function dialog box, click on the Col_index_num line.
  2. Type the function name column followed by an open round bracket.
  3. Click on cell B1 in the worksheet to enter that cell reference as the Reference argument.
  4. Type a closing round bracket to complete the COLUMN function
  5. Leave the VLOOKUP function dialog box open for the next step in the tutorial.
of 10

Entering the VLOOKUP Range Lookup Argument

The Range Lookup Argument

Ted French

VLOOKUP's Range_lookup argument is a logical value (TRUE or FALSE) 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 specific information about a particular hardware item, we will set Range_lookup equal to False.

  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 lookup formula and close dialog box.
  4. Since we have not yet entered the lookup criteria into cell D2 an #N/A error will be present in cell E2.
  5. This error will be corrected when we will add the lookup criteria in the last step of the tutorial.
of 10

Copying the Lookup Formula

Return Multiple Values with Excel VLOOKUP

Ted French

The lookup formula is intended to retrieve data from multiple columns of the data table at one time. To do this, the lookup formula must reside in all of the fields from which we want information.

In this tutorial we want it to retrieve data from columns 2, 3, and 4 of the data table — that is the price, the part number, and the supplier's name when we enter a partial name as the Lookup_value.

Since the data is laid out in a regular pattern in the worksheet, we can copy the lookup formula in cell E2 to cells F2 and G2.

As the formula is copied, Excel will update the relative cell reference in the COLUMN function (cell B1) to reflect the formula's new location.

As well, Excel doesn't change absolute cell reference $D$2 and the named range Table as the formula is copied.

There is more than one way to copy data in Excel, but probably the easiest way is by using the Fill Handle.

  1. Click on cell E2, where the lookup formula is located, to make it the active cell.
  2. Place the mouse pointer over the black square in the bottom right corner — the pointer will change to a plus sign to denote the fill handle.
  3. Click the left mouse button and drag the fill handle across to cell G2.
  4. Release the mouse button and cell F3 should contain the two-dimensional lookup formula.
  5. If done correctly, cells F2 and G2 should now also contain the #N/A error that is present in cell E2.
of 10

Entering the Lookup Criteria

Retrieving Data with the Lookup Formula

Ted French

Once the lookup formula has been copied to the required cells it can be used to retrieve information from the data table. To do so, type the name of the item you wish to retrieve into the Lookup_value cell (D2) and press the Enter key on the keyboard.

Once done, each cell containing the lookup formula should contain a different piece of data about the hardware item you are searching for.

  1. Click on cell D2 in the worksheet.
  2. Type Widget into cell D2 and press the Enter key on the keyboard.
  3. The following information should be displayed in cells E2 to G2.
    1. E2: $14.76 — the price of a widget 
    2. F2: PN-98769 — the part number for a widget 
    3. G2: Widgets Inc. — the name of the supplier for widgets
  4. Test the VLOOKUP array formula further by typing the name of other parts into cell D2 and observing the results in cells E2 to G2