■ Given monthly sales for several products, how do I write a formula that will calculate the sales of a product during a given month? For example, how much of product 2 did I sell during June?
■ Given a list of baseball player 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 spreadsheet 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 Excel MATCH func tion 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 your 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. No matter in what order the values or text in the lookup range are listed (ascending, descend ing, or no order), match type = 0 returns the row location in the lookup range that contains the first exact match to the lookup value. When no exact match exists and match type = 0, Excel returns #N/A. The great majority of MATCH function applications use match type =
0, but if match type is not included, match type = 1 is assumed.
The file MatchEx.xls, shown in Figure 5-1, contains three examples of the MATCH function’s syntax.
Figure 5-1 Examples of 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. Notice that 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 for mula 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 num ber 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).
Chapter 5 The MATCH Function 29
Unlike LOOKUP functions, the MATCH function can work with an inexact match. For example, the formula MATCH("Pho*",B4:B11,0) returns 7. The aster isk is treated as a wildcard, which means that Excel searches for the first text string in the range B4:B11 that begins with Pho.
If the lookup range is contained in a single row, Excel returns the relative position of the first match in the lookup range, moving 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
will calculate the sales of a product during a given month? For example,
how much of product 2 did I sell during June?
The file ProductLookup.xls (shown in Figure 5-2) lists sales of four NBA bob ble-head dolls for January through June. How can we write a formula that com putes the sales of a given product during a given 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.
Figure 5-2 The MATCH function can be used in combination with func tions 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 m o n t h w e ’ r e e x a m i n i n g i n c e l l B 1 0 . I n C 1 0 , w e u s e t h e f o r m u l a
MATCH(A10,A4:A7,0) to determine which row number in the range Sales con
tains 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 con tain 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 4.