Excel Two Way Lookup Using VLOOKUP Part 1

This tutorial is broken down into two parts. Following the steps listed in each part creates the two-way lookup formula seen in the image above.

The tutorial involves nesting the MATCH function inside of VLOOKUP.

Nesting a function involves entering a second function as one of the arguments for the first function.

In this tutorial, the MATCH function will be entered as the column index number argument for VLOOKUP.

Tutorial Contents

• Entering the Tutorial Data
• Creating a Named Range for the Data Table
• Starting the VLOOKUP Function
• Entering the Lookup Value Argument
• Entering the Table Array Argument
• Starting the Nested MATCH Function
• Adding Data Ranges for the MATCH Function
• Adding the Match type and Completing the MATCH Function
• Completing the VLOOKUP Function
• Adding Search Criteria to Test the Completed Formula
• Copying the Two Dimensional Lookup Formula with the Fill Handle

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 F1
• Enter the second range into cells D4 to G8

Rows 2 and 3 are left blank in order to accommodate the search criteria and the lookup formula created during this tutorial.

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

1. Enter the data as seen in the image above into cells D1 to G8

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.

Tutorial Steps

1. Highlight cells D5 to G8 in the worksheet to select them
2. Click on the Name Box located above column A
3. Type "table" (no quotes) in the Name Box
4. Press the ENTER key on the keyboard
5. Cells D5 to G8 now have the range name of "table". We will use the name for the VLOOKUP table array argument later in the tutorial

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 F2 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 list
4. Click on VLOOKUP in the list to bring up the function's dialog box

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 type of cookie we want to find information about.

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 cookie name will be located - cell D2.

Tutorial Steps

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 cookie name about which we are seeking information

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

Tutorial Steps

1. Click on the table_array line in the dialog box
2. Type "table" (no quotes) to enter the range name for this argument
3. Leave the VLOOKUP function dialog box open for the next part of the tutorial

 Continue to Part 2 >>