## Lookup with Multiple Criteria in Excel

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

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

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

### 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.- Click on cell F3 to make it the active cell. This is where we will enter the nested function.
- Click on the
*Formulas*tab of the ribbon menu. - Choose
*Lookup and Reference*from the ribbon to open the function drop down list. - Click on
*INDEX*in the list to bring up the*Select Arguments*dialog box. - Choose the
*array, row_num, col_num*option in the dialog box. - Click OK to open the INDEX function dialog box.

## Entering the INDEX Function Array Argument

### 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.- In the INDEX function dialog box, click on the
*Array*line. - Drag select cells D6 to F11 in the worksheet to enter the range into the dialog box.

## Starting the Nested MATCH Function

### 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.- In the INDEX function dialog box, click on the
*Row_num*line. - Type the function name
**match**followed by an open round bracket "**(**" - Click on cell
**D3**to enter that cell reference into the dialog box. - Type an ampersand "
**&**" after the cell reference**D3**in order to add a second cell reference. - Click on cell
**E3**to enter this second cell reference into the dialog box. - Type a comma
**" , "**after the cell reference**E3**to complete the entry of the MATCH function's*Lookup_value*argument. - Leave the INDEX function dialog box open for the next step in the tutorial.

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

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.

- Click on the
*Row_num*line after the comma to place the insertion point at the end of the current entry. - Drag select cells D6 to D11 in the worksheet to enter the range. This is the first array the function is to search.
- Type an ampersand "
**&**" after the cell references**D6:D11**because we want the function to search two arrays. - Drag select cells E6 to E11 in the worksheet to enter the range. This is the second array the function is to search.
- Type a comma
**" , "**after the cell reference**E3**to complete the entry of the MATCH function's*Lookup_array*argument. - Leave the INDEX function dialog box open for the next step in the tutorial.

## Adding the Match type and Completing the MATCH Function

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

- 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. - Type a closing round bracket "
**)**" to complete the MATCH function. - Leave the INDEX function dialog box open for the next step in the tutorial.

## Back to the INDEX Function

### 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.- Click on the
*Column_num*line in the dialog box. - 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. - 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

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

### 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.- Click on cell D3.
- Type
*Widgets*and press the**Enter**key on the keyboard. - Click on cell E3.
- Type
*Titanium*and press the**Enter**key on the keyboard. - 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. - 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.

Very thorough tutorial. I also have a website about Excel but it's geared more towards using VBA with Excel. Our content presentation is very similar. I also really like your layout. Cheers!

ReplyDeleteP.S. Check on my site: VBA Excel Tutorials