How to Create an Excel Lookup Formula with Multiple Criteria

Businessman using laptop in office meeting
Getty Images/ Ariel Skelley.

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

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

01
of 08

Prepare Your Excel Workbook with Data

Lookup Function with Multiple Criteria Excel

In order to follow the steps in this tutorial, you'll need to begin by entering the sample data into the following cells, as shown in the image above. Rows 3 and 4 are left blank in order to accommodate the array formula created during this tutorial.

  • Enter the top range of data into cells D1 to F2
  • Enter the second range into cells D5 to F11

The tutorial does not include the formatting seen in the image, but this will not affect how the lookup formula works. Information on formatting options similar to those seen above are available in this Basic Excel Formatting Tutorial.

02
of 08

Create an INDEX Function in Excel

Using Excel's INDEX Function in a Lookup Formula

The INDEX function is one of the few 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, while the Reference Form gives you the cell reference or location of the data in the table.

In this tutorial, we will use the Array Form since we want to know the name of the supplier for titanium widgets rather than the cell reference to this supplier in our database. Follow these steps to create your INDEX Function:

  1. Click on cell F3 to make it the active cell — this is where we will enter the nested function.
  2. Click on the Formulas tab of the ribbon menu.
  3. Choose Lookup and Reference from the ribbon bar to open the function drop down.
  4. Click on INDEX in the list to bring up the Select Arguments dialog box.
  5. Choose the array, row_num, col_num option in the dialog box.
  6. Click OK to open the INDEX function dialog box; this will open the Formula Builder in Excel.
  7. In the Formula Builder, click on the Array line.
  8. Highlight cells D6 to F11, by clicking and dragging, in the worksheet to enter the range into the builder.

In older versions of Excel the Formula Builder will be replaced by the Function Arguments window. Apply the same steps as mentioned in this tutorial into the window.

03
of 08

Start the Nested MATCH Function

Using Excel's MATCH Function in a Lookup Formula

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

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

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 — &.

  1. In the Formula Builder, 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.
  6. Type a comma after the cell reference E3 to complete the entry of the MATCH function's Lookup_value argument.

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

04
of 08

Complete the Nested MATCH Function

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.

Since we have identified two search fields in the Lookup_array argument we must do the same for the Lookup_array. The MATCH function only searches one array for each term specified, thus to enter multiple arrays we again use the ampersand to concatenate the arrays together.

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

  1. Click on the Row_num line after the comma to place the insertion point 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 is to search.
  3. Type an ampersand after the cell references D6:D11 because we want 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 is to search.
  5. Type a comma after the cell reference E3 to complete the entry of the MATCH function's Lookup_array argument.
  6. Leave the INDEX function dialog box open for the next step in the tutorial.
05
of 08

Add the MATCH Type Argument

Adding the Match type Argument

The third and final argument of the MATCH function is the Match_type argument; it 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 the 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 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. The Lookup_array data must be sorted in descending order.

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

  1. Following the comma on the Row_num line, type a zero — 0 — since we want the nested function to return exact matches to the terms we entered in cells D3 and E3.
  2. Type a closing round bracket — ) — to complete the MATCH function.
  3. Leave the Formula Builder open for the next step in the tutorial.
06
of 08

Finishing the INDEX Function

Entering the INDEX Function's Column_num Argument

Now that the MATCH function is done we will move to the third line of the Formula Builder and enter the last argument for the INDEX function. This third and final argument is the Column_num argument which tells Excel the column number in the range D6 to F11; this is where it will find the information we want to be returned by the function. In this case, a supplier for titanium widgets.

  1. Click on the Column_num line.
  2. Enter the number three — 3 — on this line since we are looking for data in the third column of the range D6 to F11.
  3. Again, leave the Formula Builder open for the next step in the tutorial.
07
of 08

Creating the Array Formula

Excel Lookup Array Formula

Before closing the Formula Builder, we need to turn our nested function into an array formula; this is what allows our function to search for multiple terms in the table of data. In this tutorial, we are looking to match two terms: Widgets from column 1 and Titanium from column 2.

Creating an array formula in Excel is done by pressing the CTRL, SHIFT, and ENTER keys on the keyboard at the same time. Once pressed, the function will be surrounded by curly braces, indicating that the function is now an array.

  1. Close the Formula Builder by clicking the Done button.
  2. Next, select cell F4 and press the Enter key to view the formula.
  3. To convert the Formula to an array, simultaneously press CTRL + SHIFT + ENTER on your keyboard.
  4. If done correctly a #N/A error will appear in cell F3 — the cell where we entered the function.
  5. The #N/A error appears in cell F3 because cells D3 and E3 are blank. D3 and E3 are the cells where we told the function to find the Lookup_values. Once data is added to these two cells, the error will be replaced by information from the database.
08
of 08

Adding the Search Criteria

Finding Data with the Excel Lookup Array Formula

Finally, we will add the search terms to our worksheet. As mentioned in the previous step, we are looking to match the terms Widgets from column 1 and Titanium from column 2. If, and only if, our formula finds a match for both terms in the appropriate columns in the database, will it return the value from the third column.

  1. Click on cell D3.
  2. Type Widgets and press the Enter key on the keyboard.
  3. Click on cell E3.
  4. Type Titanium and press the Enter key on the keyboard.
  5. The supplier's name Widgets Inc. should appear in cell F3 — the location of the function since it is the only supplier listed who sells Titanium Widgets.
  6. When you click on cell F3 the complete function appears in the formula bar above the worksheet.
{=INDEX (D6:F11, MATCH (D3 & E3, D6:D11 & E6:E11, 0), 3)}

In our example, there was 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.