Microsoft Office Tutorials and References

In Depth Information

**Using Functions for Lookup and Reference, Math, and Trigonometry**

Using Functions for Lookup and Reference, Math,

and Trigonometry

VLOOKUP

VLOOKUP
looks for a value in the leftmost column of a table and returns a value from the

column number that you specify.

In Figure 2.1,
VLOOKUP
references cell C9 for the reference of Compu Check, then the data

range F4:G7 is entered, followed by the column number 2 for which you want a value

returned. The result is $189,844, the corresponding value to the product Compu Check.

MATCH

MATCH
returns the position of an item in an array that matches a specified value and order or

matches the position of an item.

In Figure 2.1,
MATCH
references cell C10 for the date reference of the record to match, then

the data range C4:C7 is entered, followed by the match type—0 for record number and 1 for

record order. The result of the record number is the second record down in the list.

OFFSET

The
OFFSET
function returns a reference to a range that is a specific number of rows and

columns from a cell or range of cells.

In Figure 2.1, the
OFFSET
formula references the cell C4, 0 is the number or rows down from

the cell reference, 2 is the number of columns over from the cell reference, 1 is the height,

and 1 is the width that returns the result of the ISBN 2 columns over to the right.

SUMIF

The
SUMIF
function adds the cells specified by given criteria.

In Figure 2.1, the
SUMIF
function references the product range F4:F7, then the cell reference

to sum up which is C15, and the sums of the range that matches the specified criteria in the

range G4:G7. The result is the total dollars for the two Anti Virus Scan products of $8,041.

SUMIF
with a Conditional Month

The
SUMIF
function adds the cells specified by given criteria. The
MONTH
function is a condition

applied to the formula that finds the month specified in the condition.

In Figure 2.1, the
SUM(IF(MONTH
function references the month range of C4:C7 and then

references the condition to apply in cell C17 which is the month number(3=March). The sum

range is applied in the cell range G4:G7 and returns the result of $1,188. Note that this

formula is used in the form of an array and must be activated by pressing Ctrl+Shft+Enter.