• Share

# Tutorial: Lookup Formula with Multiple Criteria in Excel

#### 7 of 10

Adding the Match type and Completing the MATCH Function

Click on the image to view full size

### 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.
##### Excel Array Formula Tutorials
Excel's Lookup and Reference Functions

Ted French