How to Create an Excel Lookup Formula with Multiple Criteria

Businessman using laptop in office meeting

Ariel Skelley/Getty Images

Create a lookup formula that uses multiple criteria to find information in a database or table of data by using an array formula in Excel. The array formula involves nesting the MATCH function inside the INDEX function.

This article applies to Excel 2019, 2016, 2013, 2010; and Excel for Mac.

Prepare Your Excel Workbook with Data

This tutorial includes a step-by-step example that shows how to create a lookup formula that uses multiple criteria to find a supplier of titanium widgets in a sample database. Follow along to learn how to create your own custom lookup formulas.

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.

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.

This tutorial does not include the formatting seen in the image. This does not affect how the lookup formula works. Information on formatting options is available in this Basic Excel Formatting Tutorial.

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 actual 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 is where the nested function will be entered.
  2. Select Formulas.
  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 to F11 in the worksheet to enter the range into the dialog box.
A screenshot showing how to set up an array for the INDEX function in Excel

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

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 typed in 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 together 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.
A screenshot showing 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 to D11 in the worksheet to enter the range. This is the first array the function searches.
  3. Enter & (an ampersand) after the cell references D6:D11. This causes the function to search two arrays.
  4. Highlight cells E6 to E11 in the worksheet to enter the range. This 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.
  6. Leave the dialog box open for the next step in the tutorial.
A screenshot showing how to enter a MATCH argument in the INDEX function in Excel

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 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.
  3. Leave the dialog box open for the next step in the tutorial.
A screenshot showing how to enter a MATCH argument in the INDEX function in Excel

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 to F11. This 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 tells the formula to look for data in the third column of the range D6 to F11.
  3. Leave the dialog box open for the next step in the tutorial.
A screenshot showing how to enter the INDEX function's Column_num argument in Excel

Create the Array Formula

Before closing the dialog box, turn the nested function into an array formula. This 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 at the same time. 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 and place the cursor at the end of the formula in the Formula Bar.
  3. To convert the formula to an array, simultaneously 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_values. After data is added to these two cells, the error is replaced by information from the database.
A screenshot showing 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)}
A screenshot showing the results of the completed INDEX function in Excel

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