Up next in 10
Calculate Student Grades Easily: Simplify IFs with VLOOKUP in Excel
15K views
Nov 7, 2024
In this tutorial, you'll learn how to efficiently calculate student grades in Excel using the VLOOKUP function. We'll start by exploring the complexities of nested IF statements and why they can be cumbersome. Then, I'll show you a simpler, more effective method with VLOOKUP that transforms student scores into grades effortlessly.
View Video Transcript
0:00
hi everyone today we're going to learn
0:01
how to assign grades based on student
0:03
scores using the IF function and the
0:05
vlookup function I've displayed the
0:07
grade criteria in a table that you can
0:09
see on the screen for our grading scale
0:11
a score from 90 to 100 is a 80 to 89 is
0:14
B 70 to 79 is C 60 to 69 is D 50 to 59
0:20
is e and any score below 50 is f let's
0:24
start by using the IF function to assign
0:26
grades imagine a student has a score of
0:28
85% and we want to determine the grade
0:31
based on the scale shown in the table to
0:33
do this with the IF function we start by
0:35
checking one condition is the student
0:37
score greater than 89 if so we assign a
0:41
otherwise we give them F since the
0:43
student score is 85 they don't get a so
0:46
they receive an F but that's not right
0:48
we need to check more conditions to
0:49
assign the correct grade now let's
0:52
improve the formula by adding more
0:54
conditions after checking if the score
0:56
is above 89 we want to check if it's
0:58
above 79 to assign b instead of giving F
1:01
immediately we replace it with another
1:04
if statement as shown on the screen this
1:06
time since the score is 88 it fits in
1:08
the range for ab but we still need to
1:10
add more checks for other grades we add
1:13
more if conditions for scores above 69
1:15
59 and so on Here's the final formula
1:18
displayed on the screen you see this
1:21
formula can be quite long and hard to
1:23
read Excel prompted me about a typo
1:25
because I forgot to close one bracket I
1:27
clicked yes to accept the correction
1:29
made by Excel now the formula works
1:31
perfectly while it functions as intended
1:34
its length can be overwhelming
1:36
especially for beginners so let's
1:38
explore an easier way to achieve the
1:40
same result using the vlookup function
1:42
instead of using multiple if statements
1:45
we can create a simple lookup table with
1:46
score ranges and their corresponding
1:48
grades this table will have score ranges
1:50
in the First Column and their Associated
1:52
grades in the second colum since vlookup
1:55
by default finds an approximate match
1:58
it's important to organize your table
1:59
correctly
2:00
the data must be sorted from lowest to
2:02
highest value and only the lowest value
2:04
in each range should be included for
2:06
example using 70 for the range 70 to 79
2:10
these lowest values act as break points
2:13
our lookup table will include break
2:14
points like zero 50 60 70 80 and 90 to
2:19
determine grades along with an
2:21
additional column showing the grade
2:22
associated with each breakpoint here's
2:24
how the vlookup function works it takes
2:27
the student score and checks where it
2:28
falls in the first column of this table
2:31
once it finds the range it Returns the
2:33
grave from the second column the vlookup
2:35
function is designed to search for a
2:37
value in the First Column of a table and
2:39
return a corresponding value from
2:41
another column in the same Row the first
2:43
parameter is the value you want to find
2:45
the second parameter is the range of
2:46
cells containing your data the third
2:48
parameter is the column number from
2:50
which to retrieve the value and the
2:51
fourth parameter is optional allowing
2:53
for either true or false if you set it
2:56
to false the function will find only
2:58
exact matches by by default it is set to
3:01
true meaning vlookup finds an
3:03
approximate match an approximate match
3:05
means that vlookup will return the
3:07
closest Value that is less than or equal
3:09
to the lookup value making it
3:11
particularly useful for scenarios like
3:13
grading scales where you want to assign
3:14
a score based on defined ranges in our
3:17
case since we are looking for an
3:18
approximate value we won't need to
3:20
specify this parameter and that's it
3:23
we've learned how to assign grades using
3:24
both the IF function and the vlookup
3:27
function while the IF function is great
3:29
for smaller t asks but nesting if within
3:31
another if makes it difficult to
3:33
understand the formula vup makes things
3:35
faster and simpler by doing the same
3:38
task with a helper table thanks for
3:40
watching please don't forget to like
3:42
share and subscribe for more Excel tips
3:45
if you have a question don't hesitate to
3:46
ask in the comments