Double-Teaming in Excel

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) .

Exhibit 1

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.

Exhibit 2

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.

Exhibit 3

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.