# Thread: Using Vlookup to add up several values

1. I need to look up various types of Depreciation , where the values are in column 105 (Col DL) and the decriptions are in col L

I have tried to use the following formula to add all the various depreciation types in Col 105, but cannot get it to add the values

=IF(A69=(RIGHT(A69,50)),VLOOKUP("*Depreciation*",S heet1!\$L\$1512:\$DL\$1783,105,FALSE))

The various types of depreciation are:

DEPRECIATION-COMPUTER EQUIP
DEPRECIATION Corp Signage
DEPRECIATION-P & M
DEPRECIATION-F & F
DEPRECIATION-OE

your assistance will be most appreciated

2. The VLOOKUP doesn't look right. The first parameter should be the value you are looking up and the 3rd should be a column index into the array of values in the lookup table.
Also the lookup table should be at least 2 columns, with column 1 containing the lookup values and the 2nd column the value to return.

I think we need a little more explanation about how you determine what dep. type is appropriate?

cheers, Paul

3. Hi Paul

Thanks for the reply. Attached please find saple data containing amongst others the various types of depreciation (L11:l15). I allways need to reference the values to Col DL. Each month I new col Is inserted and the data is copied into Col DL

I need a formula that will addadd the depreciation values.

Your assistance will be most appreciated

4. I don't have converters for xlsx format, can you post an xls version?

cheers, Paul

5. Vlookup does not work with wildcards. I think SUMIF will work:

=SUMIF(L1512:L1783,"*Depreciation*",DL1512L1783)

This will sum the values in Col DL where L contains "Depreciation"

Steve

6. SUMIF is the way to go.

VLOOKUP will work with a wild card as the match.
However, it will still only return a value from the first match value that fits the pattern
and as such here would be of no use.

7. Originally Posted by P T
I don't have converters for xlsx format, can you post an xls version?

cheers, Paul
Hi Paul - Try this link for Microsoft converters .....

Microsoft Office Compatibility Pack

8. Thanks, Tim. I was being a bit lazy not installing the converters, but Office 2000 still does everything I need.

cheers, Paul

9. Hi Howard - Using the sample file that was attached
Try this (modification of Steve's formula) in Cell B2 on the tab named Depreciation
=SUMIF(Sheet1!L1:L16,"*depreciation*",Sheet1!DL1L16)
the answer should be 76,820.25

Tim

10. Hi Paul - Here is an .xls version of Howard's file.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•