How to Create an Excel Lookup Formula With Multiple Criteria

Find what you need in Excel fast

What to Know

  • First, create an INDEX function, then start the nested MATCH function by entering the Lookup_value argument.
  • Next, add the Lookup_array argument followed by the Match_type argument, then specify the column range.
  • Then, turn the nested function into an array formula by pressing Ctrl+Shift+Enter. Finally, add the search terms to the worksheet.

This article explains how to create a lookup formula that uses multiple criteria in Excel to find information in a database or table of data by using an array formula. The array formula involves nesting the MATCH function inside the INDEX function. Information covers Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Mac.

Follow Along With the Tutorial

To follow the steps in this tutorial, enter the sample data into the following cells, as shown in the image below. Rows 3 and 4 are left blank to accommodate the array formula created during this tutorial. (Note that this tutorial does not include the formatting seen in the image.)

Tutorial data for the Lookup function with multiple criteria in Excel
  • Enter the top range of data into cells D1 to F2.
  • Enter the second range into cells D5 to F11.

Create an INDEX Function in Excel

The INDEX function is one of the few functions in Excel that has multiple forms. The function has an Array Form and a Reference Form. The Array Form returns the data from a database or table of data. The Reference Form gives the cell reference or location of the data in the table.

In this tutorial, the Array Form is used to find the name of the supplier for titanium widgets, rather than the cell reference to this supplier in the database.

Follow these steps to create the INDEX function:

  1. Select cell F3 to make it the active cell. This cell is where the nested function will be entered.

  2. Go to Formulas.

    The Formulas menu
  3. Choose Lookup & Reference to open the function drop-down list.

  4. Select INDEX to open the Select Arguments dialog box.

  5. Choose array,row_num,column_num.

  6. Select OK to open the Function Arguments dialog box. In Excel for Mac, the Formula Builder opens.

  7. Place the cursor in the Array text box.

  8. Highlight cells D6 through F11 in the worksheet to enter the range into the dialog box.

    Leave the Function Arguments dialog box open. The formula isn't finished. You'll complete the formula in the instructions below.

    How to set up an array for the INDEX function in Excel

Start the Nested MATCH Function

When nesting one function inside another, it is not possible to open the second, or nested, function's formula builder to enter the necessary arguments. The nested function must be entered as one of the arguments of the first function.

When entering functions manually, the function's arguments are separated from each other by a comma.

The first step to enter the nested MATCH function is to enter the Lookup_value argument. The Lookup_value is the location or cell reference for the search term to be matched in the database.

The Lookup_value accepts only one search criteria or term. To search for multiple criteria, extend the Lookup_value by concatenating, or joining, two or more cell references using the ampersand symbol (&).

  1. In the Function Arguments dialog box, place the cursor in the Row_num text box.

  2. Enter MATCH(.

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

  4. Enter & (the ampersand) after the cell reference D3 to add a second cell reference.

  5. Select cell E3 to enter the second cell reference.

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

    How to enter the MATCH function as an argument for the INDEX function in Excel

    In the last step of the tutorial, the Lookup_values will be entered into cells D3 and E3 of the worksheet.

Complete the Nested 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 searches to find the Lookup_value argument added in the previous step of the tutorial.

Because two search fields were identified in the Lookup_array argument, the same must be done for the Lookup_array. The MATCH function only searches one array for each term specified. To enter multiple arrays, use the ampersand to concatenate the arrays together.

  1. Place the cursor at the end of the data in the Row_num text box. The cursor appears after the comma at the end of the current entry.

  2. Highlight cells D6 through D11 in the worksheet to enter the range. This range is the first array the function searches.

  3. Enter & (an ampersand) after the cell references D6:D11. This symbol causes the function to search two arrays.

  4. Highlight cells E6 through E11 in the worksheet to enter the range. This range is the second array the function searches.

  5. Enter , (a comma) after the cell reference E3 to complete the entry of the MATCH function's Lookup_array argument.

    How to enter a MATCH argument in the INDEX function in Excel
  6. Leave the dialog box open for the next step in the tutorial.

Add the MATCH Type Argument

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 available choices are 1, 0, or -1.

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

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

Enter these steps after the comma entered in the previous step on the Row_num line in the INDEX function:

  1. Enter 0 (a zero) after the comma in the Row_num text box. This number causes the nested function to return exact matches to the terms entered in cells D3 and E3.

  2. Enter ) (a closing round bracket) to complete the MATCH function.

    How to enter a MATCH argument in the INDEX function in Excel
  3. Leave the dialog box open for the next step in the tutorial.

Finish the INDEX Function

The MATCH function is done. It's time to move to the Column_num text box of the dialog box and enter the last argument for the INDEX function. This argument tells Excel the column number is in the range D6 through F11. This range is where it finds the information returned by the function. In this case, a supplier for titanium widgets.

  1. Place the cursor in the Column_num text box.

  2. Enter 3 (the number three). This number tells the formula to look for data in the third column of the range D6 through F11.

    How to enter the INDEX function's Column_num argument in Excel
  3. Leave the dialog box open for the next step in the tutorial.

Create the Array Formula

Before closing the dialog box, turn the nested function into an array formula. This array allows the function to search for multiple terms in the table of data. In this tutorial, two terms are matched: Widgets from column 1 and Titanium from column 2.

To create an array formula in Excel, press the CTRL, SHIFT, and ENTER keys simultaneously. Once pressed, the function is surrounded by curly braces, indicating that the function is now an array.

  1. Select OK to close the dialog box. In Excel for Mac, select Done.

  2. Select cell F3 to view the formula, then place the cursor at the end of the formula in the Formula Bar.

  3. To convert the formula to an array, press CTRL+SHIFT+ENTER.

  4. A #N/A error appears in cell F3. This is the cell where the function was entered.

  5. The #N/A error appears in cell F3 because cells D3 and E3 are blank. D3 and E3 are the cells where the function looks to find the Lookup_value. After data is added to these two cells, the error is replaced by information from the database.

    The completed INDEX function in Excel

Add the Search Criteria

The last step is to add the search terms to the worksheet. This step matches the terms Widgets from column 1 and Titanium from column 2.

If the formula finds a match for both terms in the appropriate columns in the database, it returns the value from the third column.

  1. Select cell D3.

  2. Enter Widgets.

  3. Select cell E3.

  4. Type Titanium, and press Enter.

  5. The supplier's name, Widgets Inc., appears in cell F3. This is the only supplier listed who sells Titanium Widgets.

  6. Select cell F3. The function appears in the formula bar above the worksheet.

    {=INDEX(D6:F11,MATCH(D3&E3,D6:D11&E6:E11,0),3)}

    In this example, there is only one supplier for titanium widgets. If there had been more than one supplier, the supplier listed first in the database is returned by the function.

    The results of the completed INDEX function in Excel
Was this page helpful?