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.

Search JabSto ::

Custom Search