Page 434 - Excel 2013 All-in-One For Dummies

416 Lookup and Reference
As Figure 6-3 shows, cell B12 contains a rather long and — at first glance — complex formula using the range names outlined previously and combining the INDEX and MATCH functions:
=INDEX(table_data,MATCH(part_lookup,part_list),MATCH(date_ lookup,date_list))
So you can better understand how this formula works, I break the formula down into its three major components: the first MATCH function that returns the row_num argument for the INDEX function, the second MATCH function that returns the col_num argument for the INDEX function, and the INDEX function itself that uses the values returned by the two MATCH func- tions to return the number of parts produced.
The first MATCH function that returns the row_num argument for the INDEX function is
MATCH(part_lookup,part_list)
This MATCH function uses the value input into cell B10 (named part_lookup) and looks up its position in the cell range A2:A6 (named part_list). It then returns this row number to the INDEX function as its row_num argument. In the case of the example shown in Figure 6-3 where part AB-131 is entered in the part_lookup cell in B10, Excel returns 3 as the row_num argument to the INDEX function.
The second MATCH function that returns the col_num argument for the INDEX function is
MATCH(date_lookup,date_list)
This second MATCH function uses the value input into cell B11 (named date_lookup) and looks up its position in the cell range A2:J2 (named date_list). It then returns this column number to the INDEX function as its col_num argument. In the case of the example shown in Figure 6-3 where September 1, 2010 (formatted as Sep-10), is entered in the date_lookup cell in B11, Excel returns 7 as the col_num argument to the INDEX function.
This means that for all its supposed complexity, the INDEX function shown on the Formula bar in Figure 6-3 contains the equivalent of the following formula:
=INDEX(table_data,3,7)
As Figure 6-3 shows, Excel returns 189 units as the planned production value for part AB-131 in September, 2013. You can verify that this is correct by manually counting the rows and the columns in the table_data range (cell range A2:J6). If you count down three rows (including row 2, the first row
of this range), you come to Part 131 in column A. If you then count seven