Excel Find Named Range How To Create A

This step covers adding the Lookuparray argument for the nested MATCH function.

How to Create an Excel Lookup Formula with Multiple Criteria.

Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs.

Create a lookup formula that uses multiple criteria to find information in a database or table of data by using an array formula in Excel.

The array formula involves nesting the MATCH function inside the INDEX function.

Excel Find Named Range How To Create A

This tutorial includes a step-by-step example that shows how to create a lookup formula that uses multiple criteria to find a supplier of titanium widgets in a sample database.

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.

Information on formatting options is available in this Basic Excel Formatting tutorial.

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.

When nesting one function inside another, it is not possible to open the second, or nested, functions 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 Lookupvalue argument.

The Lookupvalue is the location or cell reference for the search term to be matched in the database.

To search for multiple criteria, extend the Lookupvalue by concatenating, or joining, two or more cell references using the ampersand symbol ().

In the Function Arguments dialog box, place the cursor in the Rownum text box.

Select cell D3 to enter that cell reference into the dialog box.

Enter (the ampersand) after the cell reference D3 to add a second cell reference.

In the last step of the tutorial, the Lookupvalues will be entered into cells D3 and E3 of the worksheet.

Excel For Mac Find Named Range