Follow Along With the Tutorial
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. (Note that this tutorial does not include the formatting seen in the image.)
Enter the top range of data into cells D1 to F2. Enter the second range into cells D5 to F11.
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 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:
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 entered as one of the arguments of the first function. 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 using the ampersand symbol (&).
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. In the last step of the tutorial, the Lookup_values will be entered into cells D3 and E3 of the worksheet. 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.
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.
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:
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 through F11. This range is where it finds the information returned by the function. In this case, a supplier for titanium widgets.
Create the Array Formula
Before closing the dialog box, turn the nested function into an array formula. This array 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 simultaneously. Once pressed, the function is surrounded by curly braces, indicating that the function is now an array.
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.
