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.

### Prepare Your Excel Workbook with Data

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.

### Create an INDEX Function in Excel

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:

- 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 bar to open the function drop down. - 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; this will open the Formula Builder in Excel. - In the
**Formula Builder**, click on the**Array**line. - 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.

### 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 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**; t**his is done by concatenating or joining two or more cell references together using the ampersand symbol — **&**.

- In the
**Formula Builder**, 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. - 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.

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

- Click on the
**Row_num**line after the comma to place the insertion point at the end of the current entry. - Highlight 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. - Highlight 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.

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

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

### Finishing the INDEX Function

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

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

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

- Close the
**Formula Builder**by clicking the**Done**button. - Next, select cell
**F4**and press the**Enter**key to view the formula. - To convert the Formula to an array, simultaneously press
**CTRL + SHIFT + ENTER**on your keyboard. - If done correctly 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**. Once data is added to these two cells, the error will be replaced by information from the database.

### Adding the Search Criteria

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.

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