Need assistance fixing a VLOOKUP

[wendell42]

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
	A	B	C	D	E	F	G
1	Tax Bill #:	Owner Name:
2
3	TYPE OF EQUIPMENT	SCHEDULE # (FROM THE BACK OF FORM)	YEAR OF PURCHSE	COST	DEPRECIATION % (FROM BACK OF FORM)	APPLY SEC 179 Y/N	DEPRECIATED COST
4					0.0%		0
5					0.0%		0
6					0.0%		0
7					0.0%		0
8					0.0%		0
9					0.0%		0
10					0.0%		0
11					0.0%		0
12					0.0%		0
13					0.0%		0
14					0.0%		0
15					0.0%		0
16					0.0%		0
17					0.0%		0
18					0.0%		0
19					0.0%		0
20					0.0%		0
Sheet1

Cell Formulas
Range	Formula
E4:E20	E4	=IF(B4="",0,VLOOKUP(C4,DeprSchedulea!$A$3:$I$55,B4+1))
G4:G20	G4	=D4*E4

Cells with Data Validation
Cell	Allow	Criteria
B4:B60	List	=$IU$4:$IV$4
C4:C60	List	=$IT$2:$IT$10

PAGE 2

web_excel_for_2021.xls
	A	B	C	D	E	F	G	H	I
2	1965	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%
3	1966	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%
4	1967	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%
5	1968	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%
6	1969	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%
7	1970	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%
8	1971	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%
9	1972	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%
10	1973	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%
11	1974	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%
12	1975	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%
13	1976	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	14.0%
14	1977	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	16.0%
15	1978	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	18.0%
16	1979	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	20.0%
17	1980	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	21.0%
18	1981	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	23.0%
19	1982	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	25.0%
20	1983	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	27.0%
21	1984	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	29.0%
22	1985	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	31.0%
23	1986	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	33.0%
24	1987	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	35.0%
25	1988	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	37.0%
26	1989	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	39.0%
27	1990	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	41.0%
28	1991	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	43.0%
29	1992	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	45.0%
30	1993	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	47.0%
31	1994	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	49.0%
32	1995	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	51.0%
33	1996	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	14.0%	53.0%
34	1997	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	18.0%	54.0%
35	1998	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	21.0%	56.0%
36	1999	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	25.0%	58.0%
37	2000	13.0%	13.0%	13.0%	13.0%	13.0%	13.0%	28.0%	60.0%
38	2001	13.0%	13.0%	13.0%	13.0%	13.0%	15.0%	32.0%	62.0%
39	2002	13.0%	13.0%	13.0%	13.0%	13.0%	19.0%	35.0%	64.0%
40	2003	13.0%	13.0%	13.0%	13.0%	13.0%	23.0%	39.0%	66.0%
41	2004	13.0%	13.0%	13.0%	13.0%	13.0%	28.0%	42.0%	68.0%
42	2005	13.0%	13.0%	13.0%	13.0%	13.0%	32.0%	46.0%	70.0%
43	2006	13.0%	13.0%	13.0%	13.0%	13.0%	37.0%	49.0%	72.0%
44	2007	13.0%	13.0%	13.0%	13.0%	16.0%	41.0%	53.0%	74.0%
45	2008	13.0%	13.0%	13.0%	13.0%	22.0%	45.0%	56.0%	76.0%
46	2009	13.0%	13.0%	13.0%	13.0%	28.0%	50.0%	60.0%	78.0%
47	2010	13.0%	13.0%	13.0%	13.0%	34.0%	54.0%	63.0%	80.0%
48	2011	13.0%	17.0%	13.0%	13.0%	41.0%	58.0%	67.0%	82.0%
49	2012	13.0%	26.0%	13.0%	13.0%	47.0%	63.0%	70.0%	84.0%
50	2013	13.0%	34.0%	13.0%	13.0%	53.0%	67.0%	74.0%	86.0%
51	2014	13.0%	43.0%	13.0%	13.0%	59.0%	72.0%	77.0%	87.0%
52	2015	20.0%	52.0%	20.0%	13.0%	66.0%	76.0%	81.0%	89.0%
53	2016	34.0%	61.0%	34.0%	13.0%	72.0%	80.0%	84.0%	91.0%
54	2017	49.0%	69.0%	49.0%	13.0%	78.0%	85.0%	88.0%	93.0%
55	2018	64.0%	78.0%	64.0%	27.0%	84.0%	89.0%	91.0%	95.0%
56	2019	78.00%	87.0%	78.00%	56.00%	91.0%	93.00%	95.00%	97.0%
57	2020	93.00%	96.0%	93.00%	85.00%	97.0%	98.00%	98.00%	99.0%
DeprSchedulea

 

[Fluff]

Could you repost sheet1 with some actual data in it to show what's being entered.

 

[Toadstool]

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

 

[wendell42]

Could you repost sheet1 with some actual data in it to show what's being entered.

web_excel_for_2021.xls
	A	B	C	D	E	F	G
1	Tax Bill #:	Owner Name:
2
3	TYPE OF EQUIPMENT	SCHEDULE # (FROM THE BACK OF FORM)	YEAR OF PURCHSE	COST	DEPRECIATION % (FROM BACK OF FORM)	APPLY SEC 179 Y/N	DEPRECIATED COST
4	Equipment	2	2020	10000	78.0%		7,800
5	Tables	2	2019	5000	78.0%		3,900
6	Other stuff	2	2018	3000	78.0%		2,340
7	computer	2	2017	1000	69.0%		690
8					0.0%		0
9					0.0%		0
10					0.0%		0
11					0.0%		0
Sheet1

Cell Formulas
Range	Formula
E4:E11	E4	=IF(B4="",0,VLOOKUP(C4,DeprSchedulea!$A$3:$I$55,B4+1))
G4:G11	G4	=D4*E4

Cells with Data Validation
Cell	Allow	Criteria
B4:B60	List	=$IU$4:$IV$4
C4:C60	List	=$IT$2:$IT$10

 

[Fluff]

Thanks for that.
The problem is what Toadstool mentioned in post#3
Use

=IF(B4="",0,VLOOKUP(C4,DeprSchedulea!$A$2:$I$100,B4+1,0))

 

[wendell42]

web_excel_for_2021.xls
	A	B	C	D	E	F	G
1	Tax Bill #:	Owner Name:
2
3	TYPE OF EQUIPMENT	SCHEDULE # (FROM THE BACK OF FORM)	YEAR OF PURCHSE	COST	DEPRECIATION % (FROM BACK OF FORM)	APPLY SEC 179 Y/N	DEPRECIATED COST
4	Equipment	2	2020	10000	78.0%		7,800
5	Tables	2	2019	5000	78.0%		3,900
6	Other stuff	2	2018	3000	78.0%		2,340
7	computer	2	2017	1000	69.0%		690
8					0.0%		0
9					0.0%		0
10					0.0%		0
11					0.0%		0
Sheet1

Cell Formulas
Range	Formula
E4:E11	E4	=IF(B4="",0,VLOOKUP(C4,DeprSchedulea!$A$3:$I$55,B4+1))
G4:G11	G4	=D4*E4

Cells with Data Validation
Cell	Allow	Criteria
B4:B60	List	=$IU$4:$IV$4
C4:C60	List	=$IT$2:$IT$10

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.

 

[wendell42]

Awesome Toadstool and Fluff! That fixed the issue. Thank you both SOOOOO much!!!

 

[Fluff]

Glad we could help & thanks for the feedback.