Tutorial: Lookup Formula with Multiple Criteria in Excel

By Ted French, About.com Guide

Adding the Match type and Completing the MATCH Function

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

Tutorial Steps

For help with this example click on the image above.

These steps are to be entered after the comma entered in the previous step on the Row_num line in the INDEX function dialog box.

1. 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 enter in cells D3 and E3.


2. Type a closing round bracket " ) " to complete the MATCH function.


3. Leave the INDEX function dialog box open for the next step in the tutorial.