MATCH Function in Microsoft Excel

Excel offers a function for finding specific data in column or row called MATCH. This function will allow you to find the location within an array, versus the LOOKUP functions that provide information related to the data you are seeking but in another column or row (you can use the LOOKUP function to return values in the same column or row, but the MATCH function shortens it a bit).

The format of the MATCH function is:

=MATCH(lookup_value,lookup_array,match_type).

The lookup_value is the item you want to find within the lookup_array, like finding "apple" in a range of fruits or "green" in a list of colors. The match_type can be 1, 0 or 1. If the match_type is left blank, then it is assumed to equal 1. By using 1, MATCH will return the exact value or the value that is the largest value less than the specific item for which you are searching. In this case, the array must be in ascending order. Conversely, using -1 will provide he value that is immediately larger than the item for which you are searching, if no exact value exists. If you use -1, the array must be in descending order. If the match_type is 0, only an exact match is accepted, and if no exact match is available, #N/A! is returned. With match_type set to 0, the array can be in any order.

The order is important, because the 1 or -1 is dependent on how the data is presented. If, for example, you have a range of data in a non-sequential order, you will get the wrong answer. Let's assume that the array contains the following data:

1, 4, 7, 2, 21, 14, 5, 32, 6.

If you enter the formula =MATCH(6,{1, 4, 7, 2, 21, 14, 5, 32, 6}), you will get the answer 2. Clearly there is a number 6 at the end of the array, but because it is not an exact search and the data is not in order, the MATCH formula produces an answer you do not want. If you modify the formula to read =MATCH(6,{1, 4, 7, 2, 21, 14, 5, 32, 6},0), the answer will be 9, which is the correct location. So remember that for a NON-exact search, the order is imperative.

Once I learned to better incorporate the lookup functions, the MATCH function lost its relevance for many applications. In addition, when counting and summing, the array multiplication formulas work much better, and I will be discussing that in later articles. The point is that MATCH is good for a very limited set of basic inquiries, but ultimately, it is likely easily replaced by LOOKUP or other means. For now, it is a decent way to familiarize with the methodology Excel uses.

Finally, another formula used to determine the exact match that the MATCH formula uses, is, not surprisingly, EXACT. The format for this formula is = EXACT(text1,text2). This formula is not case sensitive, and straightforward in construct. This is another formula that is not entirely useful in most instances, because the double equal sign (= =) accomplishes the same result. For example, =EXACT(A1,B1) is the same as the formula =A1= = B1. Thus, the latter is as easy to use as the actual EXACT formula. The only rationale for mentioning this formula is to remind you that there are a myriad of formulas Excel provides, but some that may never be used or are easily replaced by better, more flexible formulas. The more experience you have in financial modeling, the more easily you will understand which formulas are the appropriate to use under the specific circumstances of your model.