Chapter 4: The MATCH Function


Overview

  • Given monthly sales for several products, how do I write a formula that returns the sales of a product during a specific month? For example, how much of Product 2 did I sell during June?

  • Given a list of baseball players’ salaries, how do I write a formula that yields the player with the highest salary? How about the player with the fifth-highest salary?

  • Given the annual cash flows from an investment project, how do I write a formula that returns the number of years required to pay back the project’s initial investment cost?

Suppose you have a worksheet with 5000 rows containing 5000 names. You need to find the name John Doe, which you know appears somewhere (and only once) in the list. Wouldn’t you like to know a formula that would return the row number at which John Doe is located? The Microsoft Office Excel 2007 MATCH function enables you to find within a given array the first occurrence of a “match” to a given text string or number. You should use the MATCH function instead of a lookup function in situations in which you want the position of a number in a range rather than the value in a particular cell. The syntax of the match function is:

Match(lookup value,lookup range,[match type])

In the explanation that follows, we’ll assume that all cells in the lookup range are located in the same column. In this syntax:

  • Lookup value is the value you’re trying to match in the lookup range.

  • Lookup range is the range you’re examining for a “match” to the lookup value.

  • Match type=1 requires the lookup range to consist of numbers listed in ascending order. The MATCH function then returns the row location in the lookup range (relative to the top of the lookup range) that contains the largest value in the range that is less than or equal to the lookup value.

  • Match type=–1 requires the lookup range to consist of numbers listed in descending order. The MATCH function returns the row location in the lookup range (relative to the top of the lookup range) that contains the last value in the range that is greater than or equal to the lookup value.

  • Match type=0 returns the row location in the lookup range that contains the first exact match to the lookup value (we will discuss how to find the second or third match in Chapter 19). When no exact match exists and match type=0, Excel returns #N/A. Most MATCH function applications use match type=0, but if match type is not included, match type=1 is assumed. Thus, we use Match type 0 when the cell contents of the lookup range is unsorted. This is the situation we usually face.

The file Matchex.xlsx, shown in Figure 4-1, contains three examples of the MATCH function’s syntax.

image from book
Figure 4-1: Using the MATCH function to locate the position of a value in a range.

In cell B13, the formula MATCH("Boston",B4:B11,0) returns 1 because the first row in the range B4:B11 contains the value Boston. Text values must be enclosed in quotation marks (""). In cell B14, the formula MATCH("Phoenix",B4:B11,0) returns 7 because cell B10 (the seventh cell in B4:B11) is the first cell in the range that matches "Phoenix." In cell E12, the formula MATCH(0,E4:E11,1) returns 4 because the last number that is less than or equal to 0 in the range E4:E11 is in cell E7 (the fourth cell in the lookup range). In cell G12, the formula MATCH(–4,G4:G11,–1) returns 7 because the last number that is greater than or equal to –4 in the range G4:G11 is contained in cell G10 (the seventh cell in the lookup range).

The MATCH function can also work with an inexact match. For example, the formula MATCH("Pho*",B4:B11,0) returns 7. The asterisk is treated as a wildcard, which means that Excel searches for the first text string in the range B4:B11 that begins with Pho. Incidentally, this same technique can be used with a lookup function. For example, in the price lookup exercise in Chapter 2, “Lookup Functions,” the formula VLOOKUP(“x*”,lookup2,2) would return the price of product X212 ($4.80).

If the lookup range is contained in a single row, Excel returns the relative position of the first match in the lookup range, moving from left to right. As shown in the following examples, the MATCH function is often very useful when it is combined with other Excel functions, such as VLOOKUP, INDEX, or MAX.

  • Given monthly sales for several products, how do I write a formula that returns the sales of a product during a specific month? For example, how much of Product 2 did I sell during June?

  • The file Productlookup.xlsx (shown in Figure 4-2) lists sales of four NBA bobble-head dolls from January through June. How can we write a formula that computes the sales of a given product during a specific month? The trick is to use one MATCH function to find the row in which the given product is located, and another MATCH function to find the column in which the given month is located. We can then use the INDEX function to return the product sales for the month.

    image from book
    Figure 4-2: The MATCH function can be used in combination with functions such as INDEX and VLOOKUP.

  • We have named the range B4:G7, which contains sales data for the dolls, as Sales. We enter the product we want to know about in cell A10, and the month in cell B10. In C10, we use the formula MATCH(A10,A4:A7,0) to determine which row number in the range Sales contains sales figures for the Kobe doll. Then, in cell D10, we use the formula MATCH(B10,B3:G3,0) to determine which column number in the range Sales contains June sales. Now that we have the row and column numbers that contain the sales figures we want, we can use the formula INDEX(Sales,C10,D10) in cell E10 to yield the piece of sales data we want. For more information on the INDEX function, see Chapter 3, “The INDEX Function.”

  • Given a list of baseball players’ salaries, how do I write a formula that yields the player with the highest salary? How about the player with the fifth-highest salary?

  • The file Baseball.xlsx (see Figure 4-3 on the next page) lists the salaries paid to 401 major league baseball players during the 2001 season. The data is not sorted by salary, and we want to write a formula that returns the name of the player with the highest salary as well as the name of the player with the fifth highest salary.

    image from book
    Figure 4-3: This example uses the MAX, MATCH, and VLOOKUP functions to find and display the highest value in a list.

  • To find the name of the player with the highest salary we proceed as follows:

    • Use the MAX function to determine the value of the highest salary.

    • Use the MATCH function to determine the row that contains the player with the highest salary.

    • Use a VLOOKUP function (keying off the data row containing the player’s salary) to look up the player’s name.

  • We have named the range C12:C412, which includes the players’ salaries, as Salaries. We’ve named the range used in our VLOOKUP function (range A12:C412) as Lookup.

  • In cell C9, we begin by finding the highest player salary ($22 million) with the formula MAX(Salaries). Next, in cell C8, we use the formula MATCH(C9,Salaries,0) to determine the “player number” of the player with the highest salary. We use match type=0 because the salaries are not listed in either ascending or descending order. We find that player number 345 has the highest salary. Finally, in cell C6, we use the function VLOOKUP(C8,Lookup,2) to find the player’s name in the second column of the lookup range. Not surprisingly, we find that Alex Rodriguez was the highest paid player in 2001.

  • To find the name of the player with the fifth-highest salary, we need a function that yields the fifth-largest number in an array. The LARGE function does that job. The syntax of the LARGE function is LARGE(cell range,k). When the LARGE function is entered this way, it returns the kth-largest number in a cell range. Thus, the formula LARGE(salaries,5) in cell D9 yields the fifth largest salary ($12.6 million). Proceeding as before, we find that Derek Jeter is the player with the fifth-highest salary. (The dl before Jeter’s name indicates that at the beginning of the season, Jeter was on the disabled list.). The function SMALL(salaries,5) would return the fifth-lowest salary.

  • Given the annual cash flows from an investment project, how do I write a formula that returns the number of years required to pay back the project’s initial investment cost?

  • The file Payback.xlsx, shown in Figure 4-4, shows the projected cash flows for an investment project over the next 15 years. We assume that in Year 1, the project required a cash outflow of $100 million. During Year 1, the project generated a cash inflow of $14 million. We expect cash flows to grow at 10 percent per year. How many years will pass before the project pays back its investment?

    image from book
    Figure 4-4: Using the MATCH function to calculate an investment’s payback period.

  • The number of years required for a project to pay back an investment is called the payback period. In high-tech industries, the payback period is often used to rank investments. (You’ll learn in Chapter 7, “Evaluating Investments by Using Net Present Value Criteria,” that payback is flawed as a measure of investment quality because it ignores the value of money over time.) For now, let’s concentrate on how to determine the payback period for our simple investment model.

  • To determine the payback period for the project, we proceed as follows:

    • In column B, we compute the cash flows for each year.

    • In column C, we compute the cumulative cash flows for each year.

  • We use the MATCH function (with match type=1) to determine the row number of the first year in which cumulative cash flow is positive. This calculation gives us the payback period.

  • We gave the cells in B1:B3 the range names listed in A1:A3. Our Year 0 cash flow (–Initial_investment) is entered in cell B5. Our Year 1 cash flow (Year_1_cf) is entered in cell B6. Copying from B7 to B8:B20 the formula B6*(1+Growth) computes the cash flow for Years 2 through 15.

  • To compute the Year 0 cumulative cash flow, we enter the formula B5 in cell C5. For later years, we calculate cumulative cash flow by using a formula such as Year t cumulative cash flow=Year t–1 cumulative cash flow+Year t cash flow. To implement this relationship, simply copy from C6 to C7:C20 the formula =C5+B6.

  • To compute the payback period, we use the MATCH function (with match type=1) to compute the last row of the range C5:C20 containing a value less than 0. This calculation will always give us the payback period. For example, if the last row in C5:C20 that contains a value less than 0 is the sixth row in the range, that means the seventh value marks the cumulative cash flow for the first year the project is paid back. Because our first year is Year 0, the payback occurs during Year 6. Therefore, the formula in cell E2, MATCH(0, C5:C20,1), yields the payback period (6 years). If any cash flows after Year 0 were negative, this method would fail because our range of cumulative cash flows would not be listed in ascending order.




Microsoft Press - Microsoft Office Excel 2007. Data Analysis and Business Modeling
MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)
ISBN: 0735623961
EAN: 2147483647
Year: 2007
Pages: 200

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net