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

✦ Use match_type 0 to find the first value that is exactly equal to the lookup_value. Note that the values in the lookup_array can be in any order when you use the 0 match_type argument.
✦ Use match_type –1 to find the smallest value that is greater than or equal to the lookup_value. Note that the values in the lookup_array must be placed in descending order when you use the –1 match_type argument.
In addition to looking up the position of the production date and part number in the column and row headings in the Production Schedule table, you need to use an INDEX function, which uses the relative row and column number position to return the number to be produced from the table itself. The INDEX function follows two different syntax forms: array and reference. You use the array form when you want a value returned from the table (as you do in this example), and you use the reference form when you want a reference returned from the table.
The syntax of the array form of the INDEX function is as follows:
INDEX(array,[row_num],[col_num])
The syntax of the reference form of the INDEX function is as follows: INDEX(reference,[row_num],[col_num],[area_num])
The array argument of the array form of the INDEX function is a range of cells or an array constant that you want Excel to use in the lookup. If this range or constant contains only one row or column, the corresponding row_num or col_num arguments are optional. If the range or array constant has more than one row or more than one column, and you specify both the row_num and the col_num arguments, Excel returns the value in the array argument that is located at the intersection of the row_num argument and the col_num argument.
For the MATCH and INDEX functions in the example shown in Figure 6-3, I assigned the following range names to the following cell ranges:
✦ table_data to the cell range A2:J6 with the production data plus column and row headings
✦ part_list to the cell range A2:A6 with the row headings in the first column of the table
✦ date_list to the cell range A2:J2 with the column headings in the first row of the table
✦ part_lookup to cell B10 that contains the name of the part to look up in the table
✦ date_lookup to cell B11 that contains the name of the production date to look up in the table
Lookup and Reference 415
Book III Chapter 6
Lookup, Information, and Text Formulas