Tutorial: Lookup Formula with Multiple Criteria in Excel

By Ted French, About.com Guide

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.

1. Click on cell D3.


2. Type Widgets and press the Enter key on the keyboard.


3. Click on cell E3.


4. Type Titanium and press the Enter key on the keyboard.


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


6. 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 B>first in the database is returned by the function.