Key to Instructions
To help readers follow the
instructions in this article, we use two
different typefaces:
Boldface type
is used to identify the names of
icons, agendas and URLs.
Sans serif type shows
commands and instructions users should
type into the computer and the names of
files. |
reating a spreadsheet to calculate
depreciation expenses for a single property class
is a piece of cake for most accountants using
Excel’s LOOKUP function (see “
Make Excel a Little Smarter ,” JofA
, Jul.03, page 73). But many CPAs might
scratch their heads for a solution if the exercise
contained more than one property class. Read on
and we’ll show you how to perform that difficult
calculation by teaming LOOKUP with MATCH.
As you can see from exhibit 1 , below,
the solution for five-year property class assets
is solved simply with
=VLOOKUP(F5,A$5:B$11,2,FALSE) . But if we
expanded the problem to include seven-year assets,
the necessary data would be dependent on the
intersection of rows (the age of the asset) and
columns (its property class). Clearly LOOKUP can’t
perform the data extraction on its own—but
exhibit 2 shows how to solve the problem by
embedding MATCH inside the LOOKUP function.
Let’s create
that command in the formula bar (fx) . As
you know the LOOKUP function extracts data from
one area of a spreadsheet and uses them in
another. If the data are organized horizontally,
we’d use HLOOKUP; and if they’re vertical, as in
this example, we’d use VLOOKUP, which contains
four parts:
Assume we want to calculate
depreciation expense under the modified
accelerated cost recovery system (MACRS) for
assets with a five-year property class.
Exhibit 1 shows how the LOOKUP command could
extract the appropriate depreciation rate from a
MACRS rate table and place it in cell I5:
I5=VLOOKUP(F5,A$5:B$11,2,FALSE) (
Editor’s note: Excel’s Function
Wizard could be recruited to develop
the formula. To open the wizard, click on
Insert, Function , which evokes
a pop-up screen that will walk you through the
process.)
Formula details:
The choice of VLOOKUP shows the table
is organized vertically.
Lookup_value = F5 commands
Excel to look up the age of the asset (six years)
in the first column of the table.
Table_array = A$5:B$11 is
the cell range of the table. Notice the absolute
cell reference ($) should be used before copying
this formula to other cells.
Col_index_num = 2 retrieves
the depreciation rate from the second column of
the table.
Range_lookup = FALSE finds
an exact age match in the table. (For applications
in which exact matches are not necessarily sought,
such as when the variable in question covers a
range of values, the range_lookup would be entered
as TRUE , which is the default. In such instances,
Excel will find the largest value less than or
equal to the lookup_value ; the data in the table
therefore must be presented in ascending order.)
Now, if we add seven-year-property class
assets, the necessary data for the depreciation
expense calculations will be dependent on the
intersection of rows (the age of the asset) and
columns (its property class). That will require
the addition of the MATCH function to find the
intersection of two data points in a table—in our
example, the age and class of an asset.We’ll use
MATCH in the col_index_num position of the LOOKUP
function. MATCH has three required elements:
Details:
Lookup_value = The value you
want to match in your table. It can be a number,
text or logical value.
Lookup_array = The range in
the table containing the value you’re seeking.
Match_type = The number, -1,
0 or 1, that specifies how Excel matches the
lookup_value with values in the lookup_array . If
match_type is 1 , MATCH finds the largest value
that is less than or equal to lookup_value .
The lookup_array must be placed in ascending
order: -2, -1, 0, 1, 2; A-Z; and FALSE, TRUE. If
match_type is 0, MATCH finds the first value that
is exactly equal to lookup_value . The
lookup_array can be in any order. If
match_type is -1 , MATCH finds the smallest value
greater than or equal to lookup_value . The
lookup_array must be placed in descending order:
TRUE, FALSE; Z-A; and 2, 1, 0, -1, -2 and so on.
If match_type is omitted, Excel assumes it’s 1.
Now let’s create the spreadsheet with
VLOOKUP and MATCH in column K to determine the
proper depreciation expense rate. VLOOKUP combines
information from two different sections of the
worksheet: Each asset’s age and depreciation class
are contained in columns H and I in the asset
depreciation schedule and the MACRS table in B3
through D11. MATCH tells VLOOKUP what column to go
to in the table to find the match (intersection)
between the age and class of the asset.
Here’s the formula to put in K4 to
automatically extract the rate for a six-year-old
asset in the five-year-class category:
K4=VLOOKUP(H4,B$3:D$11,MATCH(I4,B$3:D$3,0),FALSE).
Details:
Lookup_value = H4 looks up
the age of the asset in the first column of the
table.
Table_array = B$3:D$11 is
the cell range of the table.
Col_index_num =
MATCH(I4,B$3:D$3,0)
Lookup_value = I4 looks up
the asset class (Five) in the table.
Lookup_array = B$3:D$ 3
looks for a property class match in the first row
of the table. Be sure the first cell reference in
this row corresponds to the first column of the
table. Be careful not to use C3.
MATCH_type = 0 to find an
exact match of the asset class in I4.
Range_lookup = FALSE seeks
an exact match of the age and class of the assets
in the table. If there is no exact match, Excel
will return an error message (#N/A ).
Important: The item to be looked up (in
this example, age) should be in the first column
of the table. The item to be matched (in this
example, property class) should be in the first
row of the table.
SERIES OF EVENTS
When you execute the command in
K4, Excel refers to the table, looks up the age of
the asset and matches the appropriate property
class. It then places the appropriate depreciation
rate of 5.76% for the light truck in that cell (
exhibit 2 ). If Excel finds a
match but there is no depreciation rate listed for
the class and life of the asset, it returns a
value of 0.00% (see K5) because it concludes the
asset is fully depreciated after six years. If the
age and/or class of the asset (columns H and I)
doesn’t exist in the table, Excel will return the
#N/A error message in K6 and will not allow you to
perform calculations on ranges that contain this
error. To resolve the range-error problem,
we’ll add the ISNA and IF functions to the
formula. ISNA is written as =ISNA(value) , where
value refers to the contents of a cell.
The function returns a value of TRUE if the cell
content is #N/A and FALSE if it isn’t. Adding this
function to the front of the LOOKUP formula
results in this command:
K4=ISNA(VLOOKUP(H4,B$3:D$11,MATCH(I4,B$3:D$3,0),FALSE)).
The value in the cell in column K now will
read TRUE rather than #N/A if the LOOKUP function
fails to find an exact match; or it will read
FALSE , instead of listing the depreciation rate,
if it does find an exact match. Now we
need to add an IF function to convert TRUE so
Excel can perform computations and convert FALSE
to the identified depreciation rate for the match.
For that we’ll use the IF function, which contains
three parts:
Details:
Logical_test = Value or
expression that evaluates to TRUE or FALSE . In
our example, if ISNA finds the error value of #N/A
, it returns a TRUE value; if not, it returns a
FALSE value.
Value if_true = Value
returned if the logical_test is TRUE . For
example, if the logical_test above is TRUE , the
cell can accept a number or text because Excel can
sum a data column with either of them in it.
Value_if_false = Value
returned if the logical_test is FALSE . In our
situation, if the logical_test is FALSE , we will
want the depreciation rate to be placed into
column K. Here is the new formula for K4
to automatically insert either the depreciation
rate or a message if it finds no match for both
the age and class:
K4=IF(ISNA(VLOOKUP(H4,B$3:D$11,MATCH(I4,B$3:D$3,0),FALSE)),
0,VLOOKUP(H4,B$3:D$11,MATCH(I4,B$3:D$3,0),FALSE)).
Although it may look complicated, the
formula is just a repeat of the VLOOKUP and MATCH
formulas with a couple of adjustments. The
spreadsheet now will look like exhibit 3 ,
below. By linking
MATCH, ISNA and IF functions with LOOKUP we have
created a depreciation schedule that automatically
inserts the appropriate rate for each asset in
column K and highlights input errors by noting
when an asset’s age, combined with its matched
class, doesn’t exist in our MACRS table. We also
can now calculate depreciation expense for each
individual asset and then sum these amounts in our
asset-depreciation schedule. All these functions
are handy enhancements to LOOKUP that can help you
use Excel more effectively. JUDITH K. WELCH is an
associate professor at the University of Central
Florida, Orlando. Her e-mail address is
jwelch@mail.ucf.edu . LOIS S. MAHONEY is an
assistant professor and DANIEL R. BRICKNER is an
associate professor at Eastern Michigan
University, Ypsilanti. Their e-mail addresses are
lois.mahoney@emich.edu
and dbrickner@emich.edu
, respectively. |