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?
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Your VLOOKUP is looking for exact matches, are you sure the average in B28 exactly matches a value in B28:C140?
 

RafikiRW

New Member
Joined
Nov 20, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Can you post some sample data?
 

RafikiRW

New Member
Joined
Nov 20, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Is it possible for me to attach the file? If so, how do I do it? It's quite small.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,029
Office Version
  1. 365
Platform
  1. Windows
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.
 

RafikiRW

New Member
Joined
Nov 20, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,029
Office Version
  1. 365
Platform
  1. Windows
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,"")
 
Solution

RafikiRW

New Member
Joined
Nov 20, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
BRILLIANT!!! Thank you so much. It works like a charm.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,029
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,755
Messages
5,597,929
Members
414,193
Latest member
bb60

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
Top