Excel Two Way Lookup Using VLOOKUP Part 2

01
of 06

Starting the Nested MATCH Function

Entering the MATCH Function as the Column Index Number Argument
Entering the MATCH Function as the Column Index Number Argument. © Ted French

Return to Part 1

Entering the MATCH Function as the Column Index Number Argument

Normally VLOOKUP only returns data from one column of a data table and this column is set by the column index number argument.

However, in this example we have three columns that we wish to find data in so we need a way to easily change the column index number without editing our lookup formula.

This is where the MATCH function comes into play. It will allow us to match a column number to the field name - either January, February, or March - that we type into cell E2 of the worksheet.

Nesting Functions

The MATCH function, therefore, acts as VLOOKUP's column index number argument.

This is accomplished by nesting the MATCH function inside of VLOOKUP in the Col_index_num line of the dialog box.

Entering the MATCH Function Manually

When nesting functions, Excel doesn't allow us to open the second function's dialog box to enter its arguments.

The MATCH function, therefore, must be entered manually in Col_index_num line.

When entering functions manually, each of the function's arguments must be separated by a comma " , ".

Tutorial Steps

Entering the MATCH Function's Lookup_value Argument

The first step in entering the nested MATCH function is to enter the Lookup_value argument.

The Lookup_value will be the location or cell reference for the search term we want to match in the database.

  1. In the VLOOKUP function dialog box, click on the Col_index_num line.
  2. Type the function name match followed by an open round bracket " ( "
  3. Click on cell E2 to enter that cell reference into the dialog box.
  4. Type a comma " , " after the cell reference E3 to complete the entry of the MATCH function's Lookup_value argument.
  5. Leave the VLOOKUP function dialog box open for the next step in the tutorial.

In the last step of the tutorial the Lookup_values will be entered into cells D2 and E2 of the worksheet.

02
of 06

Adding the Lookup_array for the MATCH Function

Adding the Lookup_array for the MATCH Function
Adding the Lookup_array for the MATCH Function. © Ted French

Adding the Lookup_array for the MATCH Function

This step covers adding the Lookup_array argument for the nested MATCH function.

The Lookup_array is the range of cells that the MATCH function will search to find the Lookup_value argument added in the previous step of the tutorial.

In this example, we want the MATCH function to search cells D5 to G5 for a match to the name of the month that will be entered into cell E2.

Tutorial Steps

These steps are to be entered after the comma entered in the previous step on the Col_index_num line in the VLOOKUP function dialog box.

  1. If necessary, click on the Col_index_num line after the comma to place the insertion point at the end of the current entry.
  2. Highlight cells D5 to G5 in the worksheet to enter these cell references as the range the function is to search.
  3. Press the F4 key on the keyboard to change this range into absolute cell references. Doing so will make it possible to copy the completed lookup formula to other locations in the worksheet in the last step of the tutorial
  4. Type a comma " , " after the cell reference E3 to complete the entry of the MATCH function's Lookup_array argument.
03
of 06

Adding the Match type and Completing the MATCH Function

Excel Two Way Lookup Using VLOOKUP
Excel Two Way Lookup Using VLOOKUP. © Ted French

Adding the Match type and Completing the MATCH Function

The third and final argument of the MATCH function is the Match_type argument.

This argument tells Excel how to match the Lookup_value with values in the Lookup_array. The choices are: -1, 0, or 1.

This argument is optional. If it is omitted the function uses the default value of 1.

  • if the Match_type = 1 or is omitted: MATCH finds the largest value that is less than or equal to the Lookup_value. If this value is chosen, the Lookup_array data must be sorted in ascending order.
  • if the Match_type = 0: MATCH finds the first value that is exactly equal to the Lookup_value. The Lookup_array data can be sorted in any order.
  • if the Match_type = 1: MATCH finds the smallest value that is greater than or equal to the Lookup_value. If this value is chosen, the Lookup_array data must be sorted in descending order.

Tutorial Steps

These steps are to be entered after the comma entered in the previous step on the Row_num line in the VLOOKUP function dialog box.

  1. Following the second comma on the Col_index_num line, type a zero " 0 " since we want the nested function to return an exact match to the month enter in cell E2.
  2. Type a closing round bracket " ) " to complete the MATCH function.
  3. Leave the VLOOKUP function dialog box open for the next step in the tutorial.
04
of 06

Entering the VLOOKUP Range Lookup Argument

Entering the Range Lookup Argument
Entering the Range Lookup Argument. © Ted French

The 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 the sales figures for a particular month, we will set Range_lookup equal to False.

Tutorial Steps

  1. Click on the Range_lookup line in the dialog box
  2. Type the word False in this line to indicate that we want VLOOKUP to return an exact match for the data we are seeking
  3. Click OK to complete the two dimensional lookup formula and close dialog box
  4. Since we have not yet entered the lookup criteria into cells D2 and E2 an #N/A error will be present in cell F2
  5. This error will be corrected in the next step in the tutorial when we will add the lookup criteria in the next step of the tutorial.
05
of 06

Testing the Two Way Lookup Formula

Excel Two Way Lookup Using VLOOKUP
Excel Two Way Lookup Using VLOOKUP. © Ted French

Testing the Two Way Lookup Formula

To use the two way lookup formula to find the monthly sales data for the different cookies listed in the table array, type the cookie name into cell D2, the month into cell E2 and press the ENTER key on the keyboard.

The sales data will be displayed in cell F2.

Tutorial Steps

  1. Click on cell D2 in your worksheet
  2. Type Oatmeal into cell D2 and press the ENTER key on the keyboard
  3. Click on cell E2
  4. Type February into cell E2 and press the ENTER key on the keyboard
  5. The value $1,345 - the sales amount for Oatmeal cookies in the month of February - should be displayed in cell F2
  6. At this point, your worksheet should match the example on page 1 of this tutorial
  7. Test the lookup formula further by typing any combination of the cookie types and months present in the Table_array and the sales figures should be displayed in cell F2
  8. The last step in the tutorial covers copying the lookup formula using the Fill Handle.

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

06
of 06

Copying the Two Dimensional Lookup Formula with the Fill Handle

Excel Two Way Lookup Using VLOOKUP
Excel Two Way Lookup Using VLOOKUP. © Ted French

Copying the Two Dimensional Lookup Formula with the Fill Handle

To simplify comparing the data for different months or different cookies, the lookup formula can be copied to other cells so that multiple amounts can be shown at the same time.

Since the data is laid out in a regular pattern in the worksheet, we can copy the lookup formula in cell F2 to cell F3.

As the formula is copied, Excel will update the relative cell references to reflect the formula's new location. In this case D2 becomes D3 and E2 becomes E3,

As well, Excel keeps the absolute cell reference the same so the absolute range $D$5:$G$5 remains the same when 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

  1. Click on cell D3 in your worksheet
  2. Type Oatmeal into cell D3 and press the ENTER key on the keyboard
  3. Click on cell E3
  4. Type March into cell E3 and press the ENTER key on the keyboard
  5. Click on cell F2 to make it the active cell
  6. 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
  7. Click the left mouse button and drag the fill handle down to cell F3
  8. Release the mouse button and cell F3 should contain the two dimensional lookup formula
  9. The value $1,287 - the sales amount for Oatmeal cookies in the month of March- should be displayed in cell F3
Format
mla apa chicago
Your Citation
French, Ted. "Excel Two Way Lookup Using VLOOKUP Part 2." ThoughtCo, May. 9, 2017, thoughtco.com/two-way-lookup-using-vlookup-part-2-3123916. French, Ted. (2017, May 9). Excel Two Way Lookup Using VLOOKUP Part 2. Retrieved from https://www.thoughtco.com/two-way-lookup-using-vlookup-part-2-3123916 French, Ted. "Excel Two Way Lookup Using VLOOKUP Part 2." ThoughtCo. https://www.thoughtco.com/two-way-lookup-using-vlookup-part-2-3123916 (accessed January 16, 2018).