1. Computing

Tutorial: Lookup Formula with Multiple Criteria in Excel

By

5 of 10

Starting the Nested MATCH Function
Using Excel's MATCH Function in a Lookup Formula

Click on the image to view full size

© Ted French

Starting the Nested MATCH Function

When nesting one function inside another it is not possible to open the second or nested function's dialog box to enter the necessary arguments.

The nested function must be typed in as one of the arguments of the first function.

In this tutorial, the nested MATCH function and its arguments will be entered into the second line of the INDEX function dialog box - the Row_num line.

It is important to note that, when entering functions manually, the function's arguments are separated from each other by a comma " , ".

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.

Normally the Lookup_value accepts only one search criteria or term. In order to search for multiple criteria, we must extend the Lookup_value.

This is done by concatenating or joining two or more cell references together using the ampersand symbol " & ".

Tutorial Steps

For help with this example click on the image above.

  1. In the INDEX function dialog box, click on the Row_num line.

  2. Type the function name match followed by an open round bracket " ( "

  3. Click on cell D3 to enter that cell reference into the dialog box.

  4. Type an ampersand " & " after the cell reference D3 in order to add a second cell reference.

  5. Click on cell E3 to enter this second cell reference into the dialog box.

  6. Type a comma " , " after the cell reference E3 to complete the entry of the MATCH function's Lookup_value argument.

  7. Leave the INDEX 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 D3 and E3 of the worksheet.

Return to Index page

©2014 About.com. All rights reserved.