The array formula involves nesting theMATCHfunction inside theINDEXfunction.
(Note that this tutorial does not include theformattingseen in the image.)
Thefunctionhas an Array Form and a Reference Form.
The Array Form returns the data from a database or table of data.
The Reference Form gives thecell referenceor location of the data in the table.
Select cellF3to make it theactive cell.
This cell is where the nested function will be entered.
ChooseLookup & Referenceto bring up the function drop-down list.
SelectINDEXto open theSelect Argumentsdialog box.
Choosearray,row_num,column_num.
SelectOKto open theFunction Argumentsdialog box.
In Excel for Mac, the Formula Builder opens.
Place the cursor in theArraytext box.
Highlight cellsD6throughF11in the worksheet to enter the range into the dialog box.
Leave the Function Arguments dialog box open.
The formula isn’t finished.
You’ll complete the formula in the instructions below.
The nested function must be entered as one of the arguments of the first function.
When entering functions manually, the function’s arguments are separated from each other by a comma.
The first step to enter the nested MATCH function is to enter the Lookup_value argument.
The Lookup_value is the location orcell referencefor the search term to be matched in the database.
The Lookup_value accepts only one search criteria or term.
In theFunction Argumentsdialog box, place the cursor in theRow_numtext box.
Select cellD3to enter that cell reference into the dialog box.
Enter&(the ampersand) after the cell referenceD3to add a second cell reference.
Select cellE3to enter the second cell reference.
Complete the Nested MATCH Function
This step covers adding the Lookup_arrayargumentfor the nested MATCH function.
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.
Place the cursor at the end of the data in theRow_numtext box.
The cursor appears after the comma at the end of the current entry.
Highlight cellsD6throughD11in theworksheetto enter the range.
This range is the first array the function searches.
Enter&(an ampersand) after the cell referencesD6:D11.
This symbol causes the function to search two arrays.
Highlight cellsE6throughE11in the worksheet to enter the range.
This range is the second array the function searches.
Leave the dialog box open for the next step in the tutorial.
Add the MATCH punch in Argument
The third and final argument of theMATCH function is the Match_typeargument.
This argument 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.
This number causes the nested function to return exact matches to the terms entered in cells D3 and E3.
Enter)(a closing round bracket) to complete the MATCH function.
Finish the INDEX Function
The MATCH function is done.
This argument tells Excel the column number is in therangeD6 through F11.
This range is where it finds the information returned by the function.
In this case, a supplier for titanium widgets.
Place the cursor in theColumn_numtext box.
Enter3(the number three).
This number tells the formula to look for data in the third column of the range D6 through F11.
Create the Array Formula
Before closing the dialog box, turn the nested function into anarray 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 theCTRL,SHIFT, andENTERkeys simultaneously.
Once pressed, the function is surrounded by curly braces, indicating that the function is now an array.
SelectOKto wrap up the dialog box.
In Excel for Mac, selectDone.
To convert the formula to an array, pressCTRL+SHIFT+ENTER.
A #N/Aerror appears in cell F3.
This is the cell where the function was entered.
The #N/A error appears incellF3 because cells D3 and E3 are blank.
D3 and E3 are the cells where the function looks to find the Lookup_value.
After data is added to these two cells, the error is replaced by information from thedatabase.
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 andTitanium from column 2.
TypeTitanium, and pressEnter.
The supplier’s name, Widgets Inc., appears in cell F3.
This is the only supplier listed who sells Titanium Widgets.
The function appears in theformula barabove the worksheet.
In this example, there is only one supplier for titanium widgets.
