Excel Tutorials: How to use Lookup Formula with Multiple Criteria in Excel

Lookup with Multiple Criteria in Excel

Lookup Function with Multiple Criteria in Excel

© Ted French

For more Excel tips and tricks go to my static page:  Excel Tutorials and Tricks Series. 

Lookup with Multiple Criteria

By using an array formula in Excel we can create a lookup formula that uses multiple criteria to find information in a database or list of data.
The array formula includes 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.
Following the steps in the tutorial topics below walks you through creating and using the formula seen in the image above.

Entering the Tutorial Data

Lookup Function with Multiple Criteria Excel

© Ted French

Entering the Tutorial Data

The first step in the tutorial is to entering the data into an Excel worksheet.
In order to follow the steps in the tutorial enter the data shown in the image above into the following cells.

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

Rows 3 and 4 are left blank in order to accommodate the array formula created during this tutorial.
This tutorial does not include formatting steps for the worksheet.
Because of this your worksheet may look different than the example shown, but the array formula will give the same results.

Starting the INDEX Function

Lookup Function with Multiple Criteria Excel

© Ted French

Choosing the Array Form Argument List

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 a supplier for titanium widgets rather than the cell reference to this supplier in our database.
Each form has a different list of arguments that must be selected before beginning the function.

Tutorial Steps

For help with these instructions, see the image above.

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 to open the function drop down list.


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.

Entering the INDEX Function Array Argument

Lookup Function with Multiple Criteria Excel

© Ted French

Entering the INDEX Function Array Argument

The first argument required is the Array argument. This argument specifies the range of cells to be searched for the desired data.
For this tutorial this argument will be our sample database.

Tutorial Steps

For help with this example click on the image above.

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


2. Drag select cells D6 to F11 in the worksheet to enter the range into the dialog box.

Starting the Nested MATCH Function

Lookup Function with Multiple Criteria Excel

© 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.

Adding the Lookup_array for the MATCH Function

Lookup Function with Multiple Criteria Excel

© Ted French

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.
To enter multiple arrays we again use the ampersand " & " to concatenate the arrays together.

Tutorial Steps

For help with this example click on the image above.
These steps are to be entered after the comma entered in the previous step on the Row_num line in the INDEX function dialog box.

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


2. Drag select 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. Drag select 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.

Adding the Match type and Completing the MATCH Function

Lookup Function with Multiple Criteria Excel

© Ted French

Adding the Match type and Completing the MATCH Function

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

Tutorial Steps

For help with this example click on the image above.
These steps are to be entered after the comma entered in the previous step on the Row_num line in the INDEX function dialog box.

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 enter in cells D3 and E3.


2. Type a closing round bracket " ) " to complete the MATCH function.


3. Leave the INDEX function dialog box open for the next step in the tutorial.

Back to the INDEX Function

Lookup Function with Multiple Criteria Excel

© Ted French

Back to the INDEX Function

Now that the MATCH function is done we will move to the third line of the open dialog box 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 where it will find the information we want returned by the function. In this case, a supplier for titanium widgets.

Tutorial Steps

For help with this example click on the image above.

1. Click on the Column_num line in the dialog box.


2. Enter the number three " 3 " (no quotes) on this line since we are looking for data in the third column of the range D6 to F11.


3. Do not Click OK or close the INDEX function dialog box. It must remain open for the next step in the tutorial - creating the array formula.

Creating the Array Formula

Excel Lookup Array Formula

© Ted French

Creating the Array Formula

Before closing the dialog box we need to turn our nested function into an array formula.
An array formula is what allows it 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.
The effect of pressing these keys together is to surround the function with curly braces: { } indicating that it is now an array formula.

Tutorial Steps

For help with this example click on the image above.

1. With the completed dialog box still open from the previous step of this tutorial, press and hold down the CTRL and SHIFT keys on the keyboard then press and release the ENTER key.


2. If done correctly, the dialog box will close and a #N/A error will appear in cell F3 - the cell where we entered the function.


3. 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 in step 5 of the tutorial. Once data is added to these two cells , the error will be replaced by information from the database.

Adding the Search Criteria

Finding Data with the Excel Lookup Array Formula

© Ted French

Adding the Search Criteria

The last step in the tutorial is to 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.

Tutorial Steps

For help with this example click on the image above.

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
   {=INDEX (D6:F11, MATCH (D3 & E3, D6:D11 & E6:E11, 0), 3)}
   appears in the formula bar above the worksheet.

Note: 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.