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.

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

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

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

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

- Highlight
**cells D5**to**G10**in the worksheet to select them. - Click on the
**Name Box**located above**column A**. - Type
**Table**into 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. - Click on
**VLOOKUP**in the list to open the function's dialog box.

### Entering the Lookup Value Argument

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

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

- Click on the
**table_array**line in the dialog box. - Type
**Table**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, 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.

- In the
**VLOOKUP**function dialog box, click on theline.*Col_index_num* - 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**) 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**.

- 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

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

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

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