Bidirectional Search in Excel with VLOOKUP part 2

Content
  1. Run a nested MATCH function
  2. Run a nested MATCH function
  3. Entering the MATCH function as an argument for column order numbers
  4. nested functions
  5. Entering the MATCH function manually
  6. Entering the Lookup_value argument of the MATCH function
  7. Add lookup_array for the MATCH function
  8. Add lookup_array for the MATCH function
  9. Add a match type and complete the MATCH feature
  10. Add a match type and complete the MATCH feature
  11. Enter a search argument for the VLOOKUP range
  12. Find Range Argument
  13. Testing a two-way search formula
  14. Testing a two-way search formula
  15. Copy a 2D lookup formula with a fill handle
  16. Copy a 2D lookup formula with a fill handle

Run a nested MATCH function

Run a nested MATCH function

This is a continuation of a two-way search in Excel using VLOOKUP Part 1.

Entering the MATCH function as an argument for column order numbers

Usually VLOOKUP returns data from only one column of the data table and that column is given by the argument column index number

However, in this example, we have three columns where we want to find data, so we need a way to easily change column index number without changing the search formula.

This is where the MATCH function comes in. This allows us to match the column number with the field name (January, February, or March) that we enter in cell E2 of the worksheet.

nested functions

Therefore, the MATCH function acts as an argument column index number in VLOOKUP.

This is accomplished by nesting the MATCH function inside the VLOOKUP inside the string Col_index_num dialog box.

Entering the MATCH function manually

When nesting functions, Excel does not allow us to open the dialog box of the second function to enter the arguments.

Therefore, the MATCH function must be entered manually in the line Col_index_num

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

Entering the Lookup_value argument of the MATCH function

The first step in entering a nested MATCH function is to enter an argument lookup value

lookup value will be a location or cell reference for the search term we want to find in the database.

  1. In the VLOOKUP function dialog, click the line Col_index_num

  2. Enter function name agreement followed by an open parenthesis “

  3. Click on a cell E2 to enter a reference to that cell in the dialog box.

  4. Enter a comma after cell reference E3 to complete the function argument input lookup value MATCH functions.

  5. Leave the VLOOKUP dialog open for the next step in the tutorial.

In the last step of the tutorial, Lookup_values ​​are entered in cells D2 and E2 of the worksheet.

Add lookup_array for the MATCH function

Add lookup_array for the MATCH function

This step deals with adding an argument lookup_array for the nested MATCH function.

lookup_array is the range of cells that the MATCH function will look at to find the argument lookup value added in the previous step of the tutorial.

In this example, we want the MATCH function to search cells D5 through G5 to match the name of the month entered in cell E2.

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

  1. Click on the line if necessary Col_index_num after the comma to place the insertion point at the end of the current entry.

  2. Highlight cells D5 to G5 on the worksheet to enter these cell references as the range to perform the function.

  3. Press key F4 on the keyboard to change this range to absolute cell references. This will copy the completed search formula to other places on the worksheet in the last step of the tutorial.

  4. Enter a comma after cell reference E3 to complete the function argument input lookup_array MATCH functions.

Add a match type and complete the MATCH feature

Add a match type and complete the MATCH feature

The third and final argument to the MATCH function is the argument match_type.

This argument tells Excel how to map the Lookup_value to the values ​​in the Lookup_array. Options: -1, 0 or 1.

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

  • if Match_type = 1 or absent: MATCH finds the largest value less than or equal to Lookup_value. If this value is selected, the Lookup_array data must be sorted in ascending order.

  • if Match_type = 0: MATCH finds the first value that is exactly equal to Lookup_value. Lookup_array data can be sorted in any order.

  • if Match_type = 1: MATCH finds the smallest value greater than or equal to Lookup_value. If this value is selected, the Lookup_array data must be sorted in descending order.

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

  1. After the second comma in the line Col_index_num enter zero” 0 ‘ because we want the nested function to return an exact match for the month entered in cell E2.

  2. Fill in the closing parenthesis ““ to complete the MATCH function.

  3. Leave the VLOOKUP dialog open for the next step in the tutorial.

Enter a search argument for the VLOOKUP range

Find Range Argument

The Range_lookup argument for VLOOKUP is a boolean value (TRUE or FALSE only) that specifies whether you want VLOOKUP to find an exact or approximate match to Lookup_value.

  • If TRUE or this argument is omitted, VLOOKUP returns an exact match to Lookup_value, or if no exact match is found, VLOOKUP returns the next largest value. To do this with the formula, the data in the first column of the Table_array must be sorted in ascending order.

  • If FALSE, VLOOKUP only uses an exact match to 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 no exact match is found, an #N/A error is returned.

Since we’re looking for sales data for a specific month in this tutorial, we’ll set Range_lookup to not true

  1. Click on a line range_lookup in the dialog box.

  2. Enter a word not true on this line to indicate that we want VLOOKUP to return an exact match for the data we are looking for

  3. Click OK to complete the 2D search formula and close the dialog box

  4. Since we have not yet entered the search criteria in cells D2 and E2, there will be an #N/A error in cell F2.

  5. This bug will be fixed in the next step of the tutorial when we add the search criteria in the next step of the tutorial.

Testing a two-way search formula

Testing a two-way search formula

To use a two-way search formula to find monthly sales data for different cookies listed in a series of tables, enter the cookie name in cell D2, the month in cell E2, and press Enter on your keyboard.

Sales data is displayed in cell F2.

  1. Click on cell D2 in your worksheet.

  2. Enter Oatmeal to cell D2 and press ENTER on keyboard.

  3. Press cell E2

  4. Enter February to cell E2 and click ENTER on keyboard.

  5. Meaning $1,345 – the number of oatmeal cookie sales in February – should be displayed in cell F2.

  6. At this point, your worksheet should match the example on page 1 of this tutorial.

  7. Then test the search formula by entering a combination of cookie types and months into the Table_array, and the sales numbers should appear in cell F2.

  8. The last step in the tutorial is to copy the search formula using the fill handle.

If an error message appears in cell F2, such as: #ref! this list of VLOOKUP errors may help you diagnose the problem.

Copy a 2D lookup formula with a fill handle

Copy a 2D lookup formula with a fill handle

To make it easier to compare data from different months or different cookies, the search formula can be copied to other cells to display multiple amounts at once.

Since the data is arranged in a regular pattern on the worksheet, we can copy the search formula in cell F2 to cell F3.

As you copy the formula, Excel updates the relative cell references to reflect the new location of the formula. In this case, D2 becomes D3 and E2 becomes E3,

Excel also keeps the absolute cell reference the same, so the absolute range $D$5: $G$5 stays the same when you copy the formula.

There are several ways to copy data in Excel, but probably the easiest way is to use the fill handle.

  1. Press cell D3 on the worksheet.

  2. Enter Oatmeal to cell D3 and press the key ENTER on keyboard.

  3. Click on cell E3

  4. Enter March to cell E3 and press the key ENTER on keyboard.

  5. Press cell F2 to make it active.

  6. Place the mouse pointer on the black square in the lower right corner. The pointer changes to a plus sign “+” – this is the fill handle

  7. Click left mouse button and drag the fill handle to cell F3.

  8. Release the mouse button and cell F3 should contain the 2D lookup formula

  9. The value $1,287 – the number of oatmeal cookie sales in March – should be displayed in cell F3

Leave a Reply

Your email address will not be published.