You may have a table in Excel 2013 in which you need to perform a two-way lookup, whereby a piece of data is retrieved from the Lookup table based on looking up a value in the top row (with the table’s column headings) and a value in the first column (with the table’s row headings).

The figure illustrates a situation in which you would use two values, the production date and the part number, to look up the expected production. In the 2013 Production Schedule table, the production dates for each part form the column headings in the first row of the table, and the part numbers form the row headings in its first column of the table.

To look up the number of the part scheduled to be produced in a particular month, you need to the use the MATCH function, which returns the relative position of a particular value in a cell range or array. The syntax of the MATCH function is as follows:

MATCH(lookup_value,lookup_array,[match_type])

The *lookup_value *argument is, of course, the value whose position you want returned when a match is found, and the *lookup_array *is the cell range or array containing the values that you want to match. The optional *match_type *argument is the number 1, 0, or –1, which specifies how Excel matches the value specified by the *lookup_value *argument in the range specified by the *lookup_array *argument:

Use

*match_type 1*to find the largest value that is less than or equal to the*lookup_value**.*Note that the values in the*lookup_array*must be placed in ascending order when you use the 1*match_type*argument. (Excel uses this type of matching when the*match_type*argument is omitted from the MATCH function.)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, the following range names were assigned 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

As the figure 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))