How to use VLookup with a range of numbers?



Hi I'm trying to solve this problem I have and could use some help, I was
wondering if its possible to use VLookup to check through a range of data in
one cell, ex: 89-100 or 71-74. But I'm not sure if there is a way to make
excel understand FROM 89 TO 100 0r 71 TO 74. (Yes I've just started learning
excel on my own)

This is the problem I'm trying to solve:

To calculate the data in the 20 Point Scale column, enter a formula that
converts each student’s Final Percentage to a number grade on the 20 Point
Scale as outlined below:-

80% and above = 1
75-79% = 2
71-74% = 3
70% = 4
67-69% = 5
64-66% = 6
61-63% = 7
60% = 8
57-59% = 9
54-56% = 10
51-53% = 11
50% = 12
47-49% = 13
44-46% = 14
41-43% = 15
40% = 16
35-39% = 17
30-34% = 18
20-29% = 19
0-19% = 20

The formula used to calculate the 20 Point Scale grades should accomplish
the following:-
 If a student has “0†for their Final Percentage, they should automatically
receive an “X†in the 20 Point Scale column (use an IF function to accomplish
 Otherwise, students’ grades are calculated using the scale in the table
above (use the VLOOKUP function to accomplish this).
 The formula must use cell references. You must not use numbers or text
inside any of the formulas or functions on the worksheet.
 Create additional tables in the worksheet to accomplish this.

Hint: Create a table for the 20-point scale in a separate area of the
worksheet and use the VLOOKUP function to convert the final percentage to the
correct number on the 20-point scale. You will need to set up the correct
breakpoints in this table in order for the VLOOKUP function to work.

Would love some help or a helpful link,

Teethless mama

Create a lookup table like this:

0 20
20 19
30 18
35 17
and so on...

then use VLOOKUP function


Thanks folks problem solved, really appreciated the help.

I'm posting a 2 more problems i got would really appreciate ur help

Thanks very much

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question