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.

In the example shown in the image above, the lookup formula makes it easy to return all values - such as price, part number, and supplier - related to various pieces of hardware.

### Return Multiple Values with Excel VLOOKUP

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

### Tutorial Contents

- Entering the Tutorial Data
- Creating a Named Range for the Data Table
- Starting the VLOOKUP Function
- Entering the Lookup Value Argument using Absolute Cell References
- Entering the Table Array Argument
- Entering the Nested COLUMN Function
- Completing the VLOOKUP Function
- Copying the Lookup Formula with the Fill Handle
- Retrieving Data with the Lookup Formula

### Enter the Tutorial Data

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.

### Tutorial Steps

- Enter the data as seen in the image above into cells D1 to G10

### Creating 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 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.

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

### Tutorial Steps

- Highlight cells D5 to G10 in the worksheet to select them
- Click on the Name Box located above column A
- Type "Table" (no quotes) in the Name Box
- Press the
**ENTER**key on the keyboard - 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

### Opening the VLOOKUP Dialog Box

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

- Click on cell E2 of the worksheet - the location where the results of the two dimensional lookup formula will be displayed
- Click on the
*Formulas*tab of the ribbon - Click on the
**Lookup & Reference**option in the ribbon to open the function drop down list - Click on
*VLOOKUP*in the list to open the function's dialog box

### Entering the Lookup Value Argument using Absolute Cell References

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
- a logical value (TRUE or FALSE only)
- a number
- a cell reference to a value in the worksheet

In this example, we will enter the cell reference to where the part name will be located - cell D2.

### Absolute Cell References

In a later step in the tutorial, we will copy the lookup formula in cell E2 to cells F2 and G2.

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.

To prevent the errors, we will convert the cell reference D2 into an absolute cell reference.

Absolute cell references do not change when formulas are copied.

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**

### Tutorial Steps

- Click on the
*lookup_value*line in the dialog box - 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 - Without moving the insertion point, press the
**F4**key on the keyboard to convert D2 into the absolute cell reference $D$2 - Leave the VLOOKUP function dialog box open for the next step in the tutorial

### Entering the Table Array Argument

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)
- the second, and any additional columns, 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.

For this example, we will use range name created in step 3 of the tutorial.

### Tutorial Steps

- Click on the
*table_array*line in the dialog box - Type "Table" (no quotes) to enter the range name for this argument
- Leave the VLOOKUP function dialog box open for the next step in the tutorial

### Nesting the COLUMN Function

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

### Tutorial Steps

- In the VLOOKUP function dialog box, click on the
*Col_index_num*line - Type the function name
**column**followed by an open round bracket "**(**" - Click on cell
**B1**in the worksheet to enter that cell reference as the*Reference*argument - Type a closing round bracket "
**)**" to complete the COLUMN function - Leave the VLOOKUP function dialog box open for the next step in the tutorial

### Entering the VLOOKUP Range Lookup Argument

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 specific information about a particular hardware item, we will set Range_lookup equal to *False*.

### Tutorial Steps

- Click on the
*Range_lookup*line in the dialog box - Type the word
*False*in this line to indicate that we want VLOOKUP to return an exact match for the data we are seeking - Click OK to complete the lookup formula and close dialog box
- Since we have not yet entered the lookup criteria into cell D2 an
**#N/A error**will be present in cell E2 - This error will be corrected when we will add the lookup criteria in the last step of the tutorial

### Copying the Lookup Formula with the Fill Handle

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 part 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 (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.

### Tutorial Steps

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

### Entering the Lookup Criteria

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.

### Tutorial Steps

- Click on cell D2 in the worksheet
- Type
*Widget*into cell D2 and press the**ENTER**key on the keyboard - The following information should be displayed 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

- 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

If an error message such as ** #REF!** appears in cells E2, F2, or G2, this list of VLOOKUP error messages may help you determine where the problem lies.