Microsoft Office Tutorials and References

In Depth Information

**Functions Relevant to Lookups**

The INDEX function returns a cell from a range. The syntax for the INDEX function is

INDEX(array,row_num,column_num)

The INDEX functionâ€™s arguments are as follows:

h
array:
A range

h
row_num:
A row number within the array argument

h
column_num:
A column number within the array argument

If an array contains only one row or column, the corresponding
row_num
or
column_

num
argument is optional.

Figure 8-5 shows a worksheet with dates, day names, and amounts in columns D, E, and F. When

you enter a date in cell B1, the following formula (in cell B2) searches the dates in column D and

returns the corresponding amount from column F. The formula in B2 is

=INDEX(F2:F21,MATCH(B1,D2:D21,0))

Figure 8-5:
Using the INDEX and MATCH functions to perform a lookup.

To understand how this formula works, start with the MATCH function. This function searches the

range D2:D21 for the date in cell B1. It returns the relative row number where the date is found.

This value is then used as the second argument for the INDEX function. The result is the

corresponding value in F2:F21.