Return Multiple Values with Excel VLOOKUP

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

Enter the Tutorial Data

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

The first step in this tutorial is to enter the data into an Excel worksheet. In order to follow the steps in this tutorial, enter the data shown in the image below 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 in this tutorial are entered in row 2 of the worksheet.

Create a Named Range for the Data Table

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

A second advantage of 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 an alternative to using​ absolute cell references to prevent errors when copying formulas.

Highlight cells D5 to G10 in the worksheet. Place the cursor in the Name Box located above column A, type Table, then press Enter. Cells D5 to G10 have the range name of Table. The range name for the VLOOKUP table array argument is used later in this tutorial.

Open the VLOOKUP Dialog Box

Although it’s possible to type the 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 used in this tutorial.

As an alternative, use the VLOOKUP Function Arguments dialog box. Almost all of Excel’s functions have a dialog box where each of the function’s arguments is entered on a separate line.

Select cell E2 of the worksheet. This is the location where the results of the two-dimensional lookup formula will display. On the ribbon, go to the Formulas tab and select Lookup & Reference. Select VLOOKUP to open the Function Arguments dialog box. The Function Arguments dialog box is where the parameters of the VLOOKUP function are entered.

Enter the Lookup Value Argument

Normally, the lookup value matches a field of data in the first column of the data table. In this example, the lookup value refers to the name of the part which you 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

When formulas are copied in Excel, cell references change to reflect the new location. If this happens, D2, the cell reference for the lookup value, changes and creates errors in cells F2 and G2.

To prevent the errors, convert the cell reference D2 into an absolute cell reference. To create an absolute cell reference, press the F4 key. This adds dollar signs around the cell reference such as $D$2.

In the Function Arguments dialog box, place the cursor in the lookup_value text box. Then, in the worksheet, select cell D2 to add this cell reference to the lookup_value. Cell D2 is where the part name will be entered. Without moving the insertion point, press the F4 key to convert D2 to the absolute cell reference $D$2. Leave the VLOOKUP function dialog box open for the next step in the tutorial.

Enter the Table Array Argument

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

The first column contains the lookup value argument (which was set up in the previous section), while the second column is searched by the lookup formula to find the information you 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.

To add the table of data to the VLOOKUP function, place the cursor in the table_array text box in the dialog box and type Table to enter the range name for this argument.

Nest the COLUMN Function

Normally, VLOOKUP only returns data from one column of a data table. This column is set by the column index number argument. In this example, however, there are three columns, and the column index number needs to be changed without editing the lookup formula. To accomplish this, nest the COLUMN function inside the VLOOKUP function as the Col_index_num argument.

When nesting functions, Excel doesn’t open the second function’s dialog box to enter its arguments. The COLUMN function must be entered manually. The COLUMN function has only one argument, the Reference argument, which is a cell reference.

The COLUMN function returns the number of the column provided as the Reference argument. It converts the column letter into a number.

To find the price of an item, use the data in column 2 of the data table. This example uses column B as the Reference to insert 2 into the Col_index_num argument.

In the Function Arguments dialog box, place the cursor in the Col_index_num text box and type COLUMN(. (Be sure to include the open round bracket. ) In the worksheet, select cell B1 to enter that cell reference as the Reference argument. Type a closing round bracket to complete the COLUMN function.

Enter the VLOOKUP Range Lookup Argument

VLOOKUP’s Range_lookup argument is a logical value (TRUE or FALSE) that indicates whether VLOOKUP should find an exact or an approximate match to the Lookup_value.

TRUE or Omitted: VLOOKUP returns a close match to the Lookup_value. If an exact match is not found, VLOOKUP returns the next largest value. The data in the first column of Table_array must be sorted in ascending order. FALSE: VLOOKUP 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, specific information about a particular hardware item will be looked up, so the Range_lookup is set to FALSE.

In the Function Arguments dialog box, place the cursor in the Range_lookup text box and type False to tell VLOOKUP to return an exact match for the data.

Select OK to complete the lookup formula and close dialog box. Cell E2 will contain an #N/A error because the lookup criteria has not been entered into cell D2. This error is temporary. It will be corrected when the lookup criteria is added in the last step of this tutorial.

Copy the Lookup Formula and Enter Criteria

The lookup formula retrieves 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 you want information.

To retrieve data from columns 2, 3, and 4 of the data table (the price, the part number, and the supplier’s name), enter a partial name as the Lookup_value.

Since the data is laid out in a regular pattern in the worksheet, copy the lookup formula in cell E2 to cells F2 and G2. As the formula is copied, Excel updates the relative cell reference in the COLUMN function (cell B1) to reflect the formula’s new location. Excel doesn’t change absolute cell reference (such as $D$2) and the named range (Table) as the formula is copied.

Select cell E2, where the lookup formula is located, to make it the active cell. Drag the fill handle across to cell G2. Cells F2 and G2 display the #N/A error that is present in cell E2.  To use the lookup formulas to retrieve information from the data table, in the worksheet select cell D2, type Widget, and press Enter. The following information displays in cells E2 to G2. E2: $14. 76 — the price of a widget F2: PN-98769 — the part number for a widget G2: Widgets Inc. — the name of the supplier for widgets To test the VLOOKUP array formula, type the name of other parts into cell D2 and observe the results in cells E2 to G2. Each cell containing the lookup formula contains a different piece of data about the hardware item you searched for.

The VLOOKUP function with nested functions like COLUMN provides a powerful method to look up data inside a table, using other data as a lookup reference.

The following information displays in cells E2 to G2.

E2: $14. 76 — the price of a widget F2: PN-98769 — the part number for a widget G2: Widgets Inc. — the name of the supplier for widgets