Vlookup not working on entire content of table array

RafikiRW

New Member
Joined
Nov 20, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I am attempting to build a small template for school report cards in which the student's GPA is calculated based on the individual grades entered by the teachers.

The individual percentage grades are entered into B7:B15 and averaged in cell B28 using =IF(B7>0,VALUE(FIXED(AVERAGE(B7:B15)))*100).

This result is then referenced by a formula in another cell =VLOOKUP($B$28,$B$39:$C$140,2,FALSE)

The proper GPA is listed until the grade percentage goes below 59%. When it reaches 58%, the resulting answer becomes #N/A.

I have begun with a new spreadsheet and completely reentered the formulas by hand. I have also populated the table array (B39:C140) line by line and know that the cell formats are correct.

What am I missing?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Your VLOOKUP is looking for exact matches, are you sure the average in B28 exactly matches a value in B28:C140?
 
Upvote 0
Your VLOOKUP is looking for exact matches, are you sure the average in B28 exactly matches a value in B28:C140?
Yes, the numbers are all exact values. I have tried using TRUE instead of FALSE but it made no difference.
 
Upvote 0
Is it possible for me to attach the file? If so, how do I do it? It's quite small.
 
Upvote 0
You cannot attach files here, but you can use the XL2BB add-in to post sample data.
See the link in my signature, or the link in the reply window.
 
Upvote 0
You cannot attach files here, but you can use the XL2BB add-in to post sample data.
See the link in my signature, or the link in the reply window.
Report Card Template.xlsx
ABC
4SubjectTerm 1Term 2
5
6Bible
7French85%
8Genesis Design98%
9Health and Hygiene87%
10I.C.T.100%
11Ikinyarwanda50%
12Language Arts20%
13Life Science10%
14Maths25%
15Social Studies/History52%
16P.E.
17Art
18Drama
19Music
20Grade Point Average0.9 
21Behavior
22Neatness
23Politeness
24Cooperative Attitude
25Learning Attitude
26Attentiveness
27Perseverance
2859 
29
30Academic SubjectsNon-Academic Subjects and Behavior
31A = Excellent 90-100%E = Excellent
32B = Good 80-89%S = Satisfactory
33C = Pass 70-79%N = Needs Improvement
34D = Weak 65-69%U = Unacceptable
35F = Failing 64% and below
36
37
38PercentageGPA
391004.0
40993.9
41983.9
42973.8
43963.8
44953.7
45943.7
46933.6
47923.6
48913.5
49903.5
50893.4
51883.3
52873.2
53863.1
54853.0
55842.9
56832.8
57822.7
58812.6
59802.5
60792.4
61782.3
62772.2
63762.1
64752.0
65741.9
66731.8
67721.7
68711.6
69701.5
70691.4
71681.4
72671.3
73661.3
74651.2
75641.1
76631.1
77621.1
78611.0
79601.0
80590.9
81580.9
82570.9
83560.9
84550.9
85540.9
86530.8
87520.8
88510.8
89500.8
90490.8
91480.7
92470.7
93460.7
94450.7
95440.7
96430.7
97420.7
98410.6
99400.6
100390.6
101380.6
102370.6
103360.6
104350.5
105340.5
106330.5
107320.5
108310.5
109300.5
110290.4
111280.4
112270.4
113260.4
114250.4
115240.4
116230.3
117220.3
118210.3
119200.3
120190.3
121180.3
122170.2
123160.2
124150.2
125140.2
126130.2
127120.2
128110.1
129100.1
13090.1
13180.1
13270.1
13360.1
13450.0
13540.0
13630.0
13720.0
13810.0
13900.0
Sheet1
Cell Formulas
RangeFormula
B20:C20B20=IF(B7>0,VLOOKUP(B$28,$B$39:$C$139,2,FALSE),"")
B28B28=IF(B7>0,VALUE(FIXED(AVERAGE(B7:B15)))*100,"")
C28C28=IF(C7<>0,VALUE(FIXED(AVERAGE(C7:C15)))*100,"")
 
Upvote 0
Thanks for that, it's down to floating errors, if you replace your formula in B20 with
Excel Formula:
=SUMIFS(C39:C139,B39:B139,B28)
It should work.
Despite the fact you are rounding the values, there is an infinitesimally small remainder as can be seen in C27:C28
+Fluff v2.xlsm
ABC
4SubjectTerm 1Term 2
5
6Bible
7French0.85
8Genesis Design0.98
9Health and Hygiene0.87
10I.C.T.0.8
11Ikinyarwanda0.5
12Language Arts0.2
13Life Science0.1
14Maths0.25
15Social Studies/History0.52
16P.E.
17Art
18Drama
19Music
20Grade Point Average0.9 
21Behavior
22Neatness
23Politeness
24Cooperative Attitude
25Learning Attitude
26Attentiveness
27Perseverance56-7.10543E-05
2856-7.10543E-05
Work
Cell Formulas
RangeFormula
B20B20=SUMIFS(C39:C139,B39:B139,B28)
C20C20=IF(C7>0,VLOOKUP(C$28,$B$39:$C$139,2,FALSE),"")
B27B27=IF(B7>0,ROUND(AVERAGE(B7:B15),2)*100,"")
C27:C28C27=(56-B27)*10^10
B28B28=IF(B7>0,VALUE(FIXED(AVERAGE(B7:B15)))*100,"")
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

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