Software & Apps MS Office Excel Two Way Lookup Using VLOOKUP Part 1 by Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. our editorial process Ted French Updated on July 30, 2018 MS Office Excel Word Powerpoint Outlook Tweet Share Email By combining Excel's VLOOKUP function with the MATCH function, we can create what is known as a two-way or two-dimensional lookup formula that allows you to easily cross-reference two fields of information in a database or table of data. A two-way lookup formula is useful when you want to find or compare results for a variety of different situations. In the example shown in the image above, the lookup formula makes it easy to retrieve the sales figures for different cookies in different months simply by changing the cookie name and the month in the correct cells. 01 of 06 Find Data at the Intersection Point of a Row and Column Excel Two Way Lookup Using VLOOKUP. © Ted French 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. 02 of 06 Entering the Tutorial Data Excel Two Way Lookup Using VLOOKUP. © Ted French 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 F1Enter 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. Enter the data as seen in the image above into cells D1 to G8 03 of 06 Creating a Named Range for the Data Table Creating a Named Range in Excel. © Ted French 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. Highlight cells D5 to G8 in the worksheet to select themClick on the Name Box located above column AType "table" (no quotes) in the Name BoxPress the ENTER key on the keyboardCells 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 04 of 06 Opening the VLOOKUP Dialog Box Opening the VLOOKUP Dialog Box. © Ted French 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. Click on cell F2 of the worksheet - the location where the results of the two dimensional lookup formula will be displayedClick on the Formulas tab of the ribbonClick on the Lookup & Reference option in the ribbon to open the function drop down listClick on VLOOKUP in the list to bring up the function's dialog box 05 of 06 Entering the Lookup Value Argument Excel Two Way Lookup Using VLOOKUP. © Ted French 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 dataa logical value (TRUE or FALSE only)a numbera 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. Click on the lookup_value line in the dialog boxClick 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 06 of 06 Entering the Table Array Argument Excel Two Way Lookup Using VLOOKUP. © Ted French 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. Click on the table_array line in the dialog boxType "table" (no quotes) to enter the range name for this argumentLeave the VLOOKUP function dialog box open for the next part of the tutorial Continue to Part 2 >> Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Email Address Sign up There was an error. Please try again. You're in! Thanks for signing up. There was an error. Please try again. Thank you for signing up. Tell us why! Other Not enough details Hard to understand Submit