Microsoft Office Tutorials and References
In Depth Information
Using Functions for Lookup and Reference, Math, and Trigonometry
Using Functions for Lookup and Reference, Math,
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 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.
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.
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.