Excel tools to demonstrate

MATCH Function

The MATCH function is handy for finding a cell in a range that matches a given value. It is often used in the following situation. Suppose you have decision variable such as order quantity that needs to chosen so as to maximize some profit. You enter some formulas that link order quantity to the profit. Then you create a data table that finds the profit for a number of possible order quantities. The MATCH function lets you locate the cell in the data table with the smallest profit.

To use the MATCH function:

Enter the formula =MATCH(value,range,FALSE). This returns the index of the cell in range that matches value. For example, if the match occurs in the third cell of the range, this returns 3.

The last argument, FALSE, indicates that we want an exact match. (Actually, FALSE can be replaced by 0 with the same effect.) If there is no exact match, the formula returns an error. The MATCH function can also look for an inexact match by using third argument TRUE. I’ll let you look this up in online help.

The following spreadsheet indicates how MATCH can be used in conjunction with INDEX. You can imagine that a profit model has led to the table shown, where each order quantity listed leads to the corresponding profit. For this small example, it is obvious that the largest expected profit is $5,640, which corresponds to an order quantity of 300. However, if inputs (not shown) that drive the profit model change, the best order quantity and the corresponding profit could change. We want formulas in cells B9 and B10 to show and the best profit and best order quantity regardless of where they appear in the table.

The formula in cell B10 is =INDEX(A3:A7,MATCH(B9,B3:B7,0)). The whole purpose of the MATCH function here is to provide the row index for the INDEX function. That is, we know the best order quantity is one of the values in the range A3:A7, and we need to know which one. So we find a match for the maximum value in B3:B7 to the values in B3:B7. In this case, the maximum is the third profit, so the formula is equivalent to =INDEX(A3:A7,3).

Try it! The table below shows the profit for each order quantity and each unit stockout cost (along the top). Enter formulas in the range B9:E9 to find the best order quantity for each unit stockout cost. (Scroll down to see the answer.)