- Create a lookup formula that returns multiple values from a data entry.
- Return multiple values in Excel VLOOKUP
- Enter training data
- Create a named range for the data table
- Open the VLOOKUP dialog
- Enter the search value argument
- Absolute Cell References
- Enter the argument of the table array
- Socket Column Function:
- Enter VLOOKUP Range Search argument
- Copy the search formula and enter the criteria
Create a lookup formula that returns multiple values from a data entry.
By combining the Excel VLOOKUP function with the COLUMN function, you can create a lookup formula that returns multiple values from a single row in a database or data table. Learn how to create a lookup formula that returns multiple values from a single data record.
The instructions in this article apply to Excel 2019, 2016, 2013, 2010; and Excel for Office 365.
Return multiple values in Excel VLOOKUP
The search formula requires the COLUMN function to be nested in VLOOKUP. When nesting functions, the second function is entered as one of the arguments to the first function.
Enter training data
In this tutorial, the COLUMN function is entered as a numeric column index argument for VLOOKUP. The last step in the tutorial involves copying the search formula into additional columns to get additional values for the selected component.
The first step in this tutorial is to enter data into an Excel spreadsheet. To complete the steps in this guide, enter the data in the image below into the following cells:
Enter the top data range in cells D1 to G1.
Enter the second range in cells D4 through G10.
The search criteria and search formula created in this tutorial are entered in row 2 of the worksheet.
This guide does not include the standard Excel formatting shown in the figure, but it will not affect how the search formula works.
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. Enter a range name instead of entering cell references for data.
The second advantage of using a named range is that the cell references for that range never change, even if the formula is copied to other cells on the worksheet. Range names are an alternative to using absolute cell references to avoid mistakes when copying formulas.
The range name does not contain headers or field names for data (as shown in line 4), only data.
highlight cells of D5 before G10 on the sheet.
Place the cursor in the name field above column A, enter Table † and press Input † Cells D5 to G10 have a series of table names.
The scope name for the VLOOKUP table array argument is used later in this tutorial.
Open the VLOOKUP dialog
While a search formula can be typed directly into a cell on a worksheet, many people find it difficult to keep the syntax straight, especially for a formula as complex as the one used in this tutorial.
You can also use the VLOOKUP Function Arguments dialog box. Almost all Excel functions have a dialog box where each of the function’s arguments is entered on a separate line.
Select cell E2 on the sheet. Displays the results of the 2D search formula.
On the ribbon, go to the tab formulas and select Search and help †
Select VLOOKUP to open the dialog Function arguments †
In the Function Arguments dialog box, the parameters for the VLOOKUP function are entered.
Enter the search value argument
Usually value for the query, matches the data field in the first column of the data table. In this example, the value is search refers to the name of the part you want to find information about. Valid data types for lookup value : text data, booleans, numbers, and cell references.
Absolute Cell References
When formulas are copied to Excel, the cell references change to reflect the new location. If this happens, D2, cell reference for the lookup value changes and makes mistakes in cells F2 and G2.
Absolute cell references do not change when formulas are copied.
To avoid errors, convert the cell reference D2 to an absolute cell reference. Press F4 to create an absolute cell reference. This adds dollar signs around the cell reference, such as $D$2.
In the Function Arguments dialog box, place the cursor in the text box lookup value .Then select on the sheet cell D2 to add this cell reference to lookup value † Cell D2 contains the name of the part.
Press on the button . without moving the insertion point F4 to convert D2 to an absolute cell reference $D$2.
Leave the VLOOKUP dialog open for the next step in the tutorial.
Enter the argument of the table array
An array of tables is a table of data in which the search formula finds the desired information. The table array must contain at least two data columns.
The first column contains the search value argument (set up in the previous section), and the second column is searched for the search formula to find the information you entered.
The table array argument must be entered as a range of cell references for the data table, or as a range name.
To add a data table to the VLOOKUP function, place the cursor in the text field table_array in the dialog box and enter . in Table to enter the range name for this argument.
Socket Column Function:
Usually VLOOKUP returns data from only one column of the data table. This column is set by the column index number argument. However, in this example, there are three columns and the column index number must be changed without editing the search formula. To do this, nest the COLUMN function in the VLOOKUP function as the Col_index_num argument.
When nesting functions, Excel does not open the dialog box for the second function to enter the 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 specified as the Reference argument. It converts the column letter into a number.
Use the information in column 2 of the data sheet to find out the price of an item. In this example, column B is used as a reference to insert 2 into the Col_index_num argument.
In the dialog box Function arguments place the cursor in the text field Col_index_num and type COLUMN († (Be sure to include the open parentheses.)
Select on the sheet cell B1 to enter a link to that cell as the Link argument.
Enter close parenthesis to complete the COLUMN function.
Enter VLOOKUP Range Search argument
The Range_lookup argument for VLOOKUP is a boolean value (TRUE or FALSE) that specifies whether VLOOKUP should find an exact or approximate match to Lookup_value.
- TRUE or omitted : VLOOKUP returns a close match to Lookup_value. If no exact match is found, VLOOKUP returns the next highest value. The data in the first column of Table_array must be sorted in ascending order.
- FALSE † VLOOKUP uses an exact match to 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 no exact match is found, an #N/A error is returned.
This guide will look up information specific to a particular device, so Range_lookup is set to FALSE.
In the Function Arguments dialog box, place your cursor in the Range_lookup text box and type not true so that VLOOKUP returns an exact match for the data.
Select Okay to complete the search formula and close the dialog box. Cell E2 contains a #N/A error because no search criteria were entered in cell D2. This error is temporary. This will be fixed when the search criteria are added in the last step of this tutorial.
Copy the search formula and enter the criteria
The lookup formula retrieves data from multiple columns of a data table simultaneously. To do this, the search formula must be in all the fields from which you want to extract information.
To retrieve data from columns 2, 3, and 4 of the datasheet (price, part number, and supplier name), enter a partial name as the Lookup_value.
Since the data is arranged in a regular pattern on the worksheet, copy the search formula to cell E2 in cells F2 and G2 † While the formula is being copied, Excel updates the relative cell reference in the COLUMN function (cell B1) to reflect the new location of the formula. Excel does not change the absolute cell reference (e.g. $D$2) and a named range (table) when copying a formula.
There are several ways to copy data to Excel, but the easiest way is to use the fill handle.
Select cell E2 where the search formula is to make it the active cell.
Drag the fill handle over cell G2 † Cells F2 and G2 represent the #N/A error present in cell E2.
To use lookup formulas to retrieve information from a data table, select on a worksheet cell D2 enter widget and press Input †
The following information is displayed in cells E2 through G2.
- E2: $14.76 – Widget Price
- F2: PN-98769 – Widget Part Number
- G2: Widgets Inc. – the name of the widget provider
To test the VLOOKUP array formula, enter the names of the other parts in cell D2 and view the results in cells E2-G2.
Each cell with the search formula contains different information about the device being searched for.
The VLOOKUP function with nested functions such as COLUMN provides a powerful method for looking up data in a table using other data for reference.