Need assistance fixing a VLOOKUP

W

wendell42

Board Regular
Joined
Feb 10, 2005
Messages
137
I am trying to fix a spreadsheet that someone else (I have no idea who) built. Our constituents use the sheet to help calculate depreciated values of equipment they use in businesses. The sheet has two pages, the first page is the worksheet, the second page lists the depreciation for each schedule number. The way the sheet is SUPPOSED to work is the user will add the depreciation schedule, year purchased and original cost. the sheet will then put the percentage based on that schedule and year on page 2. Now the problem. for some reason, if the user puts in say, schedule 2 with a purchase year of 2020, it is adding the percentage of 78% when the depreciation table for schedule 2 for 2020 shows a percentage of 96%. I don't understand the VLOOKUP and I can't understand why it is pulling data from several years prior to the one I am trying to get the data from. I will attach a mini sheet with part of the worksheet and of the depreciation tables on page 2. Any help would be greatly appreciated.

web_excel_for_2021.xls
ABCDEFG
1Tax Bill #: Owner Name:
2
3TYPE OF EQUIPMENTSCHEDULE # (FROM THE BACK OF FORM)YEAR OF PURCHSECOSTDEPRECIATION % (FROM BACK OF FORM)APPLY SEC 179 Y/NDEPRECIATED COST
40.0%0
50.0%0
60.0%0
70.0%0
80.0%0
90.0%0
100.0%0
110.0%0
120.0%0
130.0%0
140.0%0
150.0%0
160.0%0
170.0%0
180.0%0
190.0%0
200.0%0
Sheet1
Cell Formulas
RangeFormula
E4:E20E4=IF(B4="",0,VLOOKUP(C4,DeprSchedulea!$A$3:$I$55,B4+1))
G4:G20G4=D4*E4
Cells with Data Validation
CellAllowCriteria
B4:B60List=$IU$4:$IV$4
C4:C60List=$IT$2:$IT$10



PAGE 2

web_excel_for_2021.xls
ABCDEFGHI
2196513.0%13.0%13.0%13.0%13.0%13.0%13.0%13.0%
3196613.0%13.0%13.0%13.0%13.0%13.0%13.0%13.0%
4196713.0%13.0%13.0%13.0%13.0%13.0%13.0%13.0%
5196813.0%13.0%13.0%13.0%13.0%13.0%13.0%13.0%
6196913.0%13.0%13.0%13.0%13.0%13.0%13.0%13.0%
7197013.0%13.0%13.0%13.0%13.0%13.0%13.0%13.0%
8197113.0%13.0%13.0%13.0%13.0%13.0%13.0%13.0%
9197213.0%13.0%13.0%13.0%13.0%13.0%13.0%13.0%
10197313.0%13.0%13.0%13.0%13.0%13.0%13.0%13.0%
11197413.0%13.0%13.0%13.0%13.0%13.0%13.0%13.0%
12197513.0%13.0%13.0%13.0%13.0%13.0%13.0%13.0%
13197613.0%13.0%13.0%13.0%13.0%13.0%13.0%14.0%
14197713.0%13.0%13.0%13.0%13.0%13.0%13.0%16.0%
15197813.0%13.0%13.0%13.0%13.0%13.0%13.0%18.0%
16197913.0%13.0%13.0%13.0%13.0%13.0%13.0%20.0%
17198013.0%13.0%13.0%13.0%13.0%13.0%13.0%21.0%
18198113.0%13.0%13.0%13.0%13.0%13.0%13.0%23.0%
19198213.0%13.0%13.0%13.0%13.0%13.0%13.0%25.0%
20198313.0%13.0%13.0%13.0%13.0%13.0%13.0%27.0%
21198413.0%13.0%13.0%13.0%13.0%13.0%13.0%29.0%
22198513.0%13.0%13.0%13.0%13.0%13.0%13.0%31.0%
23198613.0%13.0%13.0%13.0%13.0%13.0%13.0%33.0%
24198713.0%13.0%13.0%13.0%13.0%13.0%13.0%35.0%
25198813.0%13.0%13.0%13.0%13.0%13.0%13.0%37.0%
26198913.0%13.0%13.0%13.0%13.0%13.0%13.0%39.0%
27199013.0%13.0%13.0%13.0%13.0%13.0%13.0%41.0%
28199113.0%13.0%13.0%13.0%13.0%13.0%13.0%43.0%
29199213.0%13.0%13.0%13.0%13.0%13.0%13.0%45.0%
30199313.0%13.0%13.0%13.0%13.0%13.0%13.0%47.0%
31199413.0%13.0%13.0%13.0%13.0%13.0%13.0%49.0%
32199513.0%13.0%13.0%13.0%13.0%13.0%13.0%51.0%
33199613.0%13.0%13.0%13.0%13.0%13.0%14.0%53.0%
34199713.0%13.0%13.0%13.0%13.0%13.0%18.0%54.0%
35199813.0%13.0%13.0%13.0%13.0%13.0%21.0%56.0%
36199913.0%13.0%13.0%13.0%13.0%13.0%25.0%58.0%
37200013.0%13.0%13.0%13.0%13.0%13.0%28.0%60.0%
38200113.0%13.0%13.0%13.0%13.0%15.0%32.0%62.0%
39200213.0%13.0%13.0%13.0%13.0%19.0%35.0%64.0%
40200313.0%13.0%13.0%13.0%13.0%23.0%39.0%66.0%
41200413.0%13.0%13.0%13.0%13.0%28.0%42.0%68.0%
42200513.0%13.0%13.0%13.0%13.0%32.0%46.0%70.0%
43200613.0%13.0%13.0%13.0%13.0%37.0%49.0%72.0%
44200713.0%13.0%13.0%13.0%16.0%41.0%53.0%74.0%
45200813.0%13.0%13.0%13.0%22.0%45.0%56.0%76.0%
46200913.0%13.0%13.0%13.0%28.0%50.0%60.0%78.0%
47201013.0%13.0%13.0%13.0%34.0%54.0%63.0%80.0%
48201113.0%17.0%13.0%13.0%41.0%58.0%67.0%82.0%
49201213.0%26.0%13.0%13.0%47.0%63.0%70.0%84.0%
50201313.0%34.0%13.0%13.0%53.0%67.0%74.0%86.0%
51201413.0%43.0%13.0%13.0%59.0%72.0%77.0%87.0%
52201520.0%52.0%20.0%13.0%66.0%76.0%81.0%89.0%
53201634.0%61.0%34.0%13.0%72.0%80.0%84.0%91.0%
54201749.0%69.0%49.0%13.0%78.0%85.0%88.0%93.0%
55201864.0%78.0%64.0%27.0%84.0%89.0%91.0%95.0%
56201978.00%87.0%78.00%56.00%91.0%93.00%95.00%97.0%
57202093.00%96.0%93.00%85.00%97.0%98.00%98.00%99.0%
DeprSchedulea
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Could you repost sheet1 with some actual data in it to show what's being entered.
 
Upvote 0
Hi Wendell42,

Your search range only goes to row 55 and you don't have an exact match specified in the VLOOKUP so you're getting the result from row 55.

Put this into E4 and copy down

=IF(B4="",0,VLOOKUP(C4,DeprScheduleA!$A$3:$I$99,B4+1,FALSE))
 
Last edited:
Upvote 0
Fluff said:
Could you repost sheet1 with some actual data in it to show what's being entered.
Click to expand...
web_excel_for_2021.xls
ABCDEFG
1Tax Bill #: Owner Name:
2
3TYPE OF EQUIPMENTSCHEDULE # (FROM THE BACK OF FORM)YEAR OF PURCHSECOSTDEPRECIATION % (FROM BACK OF FORM)APPLY SEC 179 Y/NDEPRECIATED COST
4Equipment220201000078.0%7,800
5Tables22019500078.0%3,900
6Other stuff22018300078.0%2,340
7computer22017100069.0%690
80.0%0
90.0%0
100.0%0
110.0%0
Sheet1
Cell Formulas
RangeFormula
E4:E11E4=IF(B4="",0,VLOOKUP(C4,DeprSchedulea!$A$3:$I$55,B4+1))
G4:G11G4=D4*E4
Cells with Data Validation
CellAllowCriteria
B4:B60List=$IU$4:$IV$4
C4:C60List=$IT$2:$IT$10
 
Upvote 0
Thanks for that.
The problem is what Toadstool mentioned in post#3
Use
Excel Formula: 
=IF(B4="",0,VLOOKUP(C4,DeprSchedulea!$A$2:$I$100,B4+1,0))
 
Upvote 0
wendell42 said:
web_excel_for_2021.xls
ABCDEFG
1Tax Bill #: Owner Name:
2
3TYPE OF EQUIPMENTSCHEDULE # (FROM THE BACK OF FORM)YEAR OF PURCHSECOSTDEPRECIATION % (FROM BACK OF FORM)APPLY SEC 179 Y/NDEPRECIATED COST
4Equipment220201000078.0%7,800
5Tables22019500078.0%3,900
6Other stuff22018300078.0%2,340
7computer22017100069.0%690
80.0%0
90.0%0
100.0%0
110.0%0
Sheet1
Cell Formulas
RangeFormula
E4:E11E4=IF(B4="",0,VLOOKUP(C4,DeprSchedulea!$A$3:$I$55,B4+1))
G4:G11G4=D4*E4
Cells with Data Validation
CellAllowCriteria
B4:B60List=$IU$4:$IV$4
C4:C60List=$IT$2:$IT$10
Click to expand...


In this example, the first 2 rows are incorrect. the first row (2020 purchase date) should be 96%, the second row (2019 purchase date) should be 87% and the third and fourth row (2018 and 2019 purchase date) are correct.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Matrix returns wrong values
Replies
3
Views
237
Peter_SSs
Peter_SSs
B
  • Solved
Average based on time
2
Replies
11
Views
1K
baqer
B
P
  • Solved
Issues with negative numbers in Polynomial Trendline Equation
Replies
3
Views
171
PerezM
P
B
  • Question
Not sure this can even be done
Replies
1
Views
581
FDibbins
FDibbins
K
  • Question
Weighted Average Help!
2
Replies
13
Views
2K
wigi
W

Forum statistics

Threads
1,214,653
Messages
6,120,750
Members
448,989
Latest member
mariah3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top