Need assistance fixing a VLOOKUP

wendell42 · Feb 18, 2021

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

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

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%

3

1966

13.0%

4

1967

13.0%

5

1968

13.0%

6

1969

13.0%

7

1970

13.0%

8

1971

13.0%

9

1972

13.0%

10

1973

13.0%

11

1974

13.0%

12

1975

13.0%

13

1976

13.0%

14.0%

14

1977

13.0%

16.0%

15

1978

13.0%

18.0%

16

1979

13.0%

20.0%

17

1980

13.0%

21.0%

18

1981

13.0%

23.0%

19

1982

13.0%

25.0%

20

1983

13.0%

27.0%

21

1984

13.0%

29.0%

22

1985

13.0%

31.0%

23

1986

13.0%

33.0%

24

1987

13.0%

35.0%

25

1988

13.0%

37.0%

26

1989

13.0%

39.0%

27

1990

13.0%

41.0%

28

1991

13.0%

43.0%

29

1992

13.0%

45.0%

30

1993

13.0%

47.0%

31

1994

13.0%

49.0%

32

1995

13.0%

51.0%

33

1996

13.0%

14.0%

53.0%

34

1997

13.0%

18.0%

54.0%

35

1998

13.0%

21.0%

56.0%

36

1999

13.0%

25.0%

58.0%

37

2000

13.0%

28.0%

60.0%

38

2001

13.0%

15.0%

32.0%

62.0%

39

2002

13.0%

19.0%

35.0%

64.0%

40

2003

13.0%

23.0%

39.0%

66.0%

41

2004

13.0%

28.0%

42.0%

68.0%

42

2005

13.0%

32.0%

46.0%

70.0%

43

2006

13.0%

37.0%

49.0%

72.0%

44

2007

13.0%

16.0%

41.0%

53.0%

74.0%

45

2008

13.0%

22.0%

45.0%

56.0%

76.0%

46

2009

13.0%

28.0%

50.0%

60.0%

78.0%

47

2010

13.0%

34.0%

54.0%

63.0%

80.0%

48

2011

13.0%

17.0%

13.0%

41.0%

58.0%

67.0%

82.0%

49

2012

13.0%

26.0%

13.0%

47.0%

63.0%

70.0%

84.0%

50

2013

13.0%

34.0%

13.0%

53.0%

67.0%

74.0%

86.0%

51

2014

13.0%

43.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%

99.0%

DeprSchedulea

Fluff · Feb 18, 2021

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

Toadstool · Feb 18, 2021

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 · Feb 18, 2021

Fluff said:
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

8

9

10

11

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 · Feb 18, 2021

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

wendell42 · Feb 18, 2021

wendell42 said:
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 · Feb 18, 2021

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

Fluff · Feb 18, 2021

Glad we could help & thanks for the feedback.

Need assistance fixing a VLOOKUP

wendell42

Board Regular

Excel Facts

Fluff

MrExcel MVP, Moderator

Toadstool

Well-known Member

wendell42

Board Regular

Fluff

MrExcel MVP, Moderator

wendell42

Board Regular

wendell42

Board Regular

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Need assistance fixing a VLOOKUP

Board Regular

Excel Facts

MrExcel MVP, Moderator

Well-known Member

Board Regular

MrExcel MVP, Moderator

Board Regular

Board Regular

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock