Excel Two Way Lookup Using VLOOKUP Part 2

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. If this value is chosen, 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. If this value is chosen, the Lookup_array data must be sorted in descending order.

Tutorial Steps

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

1. Following the second comma on the Col_index_num line, type a zero " 0 " since we want the nested function to return an exact match to the month enter in cell E2.


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


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

Previous

• Starting the Nested MATCH Function
• Adding the Lookup_array for the MATCH Function
• Adding the Match type and Completing the MATCH Function
• Entering the VLOOKUP Range Lookup Argument
• Testing the Two Way Lookup Formula
• Copying the Two Dimensional Lookup Formula with the Fill Handle

Next