# VLOOKUP problem

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### raulduke

Banned
I am trying to create a VLOOKUP function to display a letter grade corresponding to a student's GPA. I entered the lookup value as the GPA, the array as the data table with letter grades and their corresponding values, and the column index as 1 as that is where the letter grades are located in the array. I keep getting N/A errors when I execute the function. I am following a video tutorial exactly, and where the tutorial gets a letter grade, I just get an error. Here are screenshots with my function. The lookup is on worksheet 1 and the array is on sheet 2 (lookup).

http://i.imgur.com/RGdNn.jpg
http://i.imgur.com/Ox4MU.jpg

#### etaf

Wayne
Moderator
in the images - you are looking up B13 - which is 76.18

then looking for that value in the lookup table D20 - E24

in the first column you have A to F

so it is looking for 76.10 in that column - but as you only have A to F - it cant find 76.10 - so hence the error

you need to change the way you are looking the grades up

can you describe the range of grades you are looking to use vlookup for

#### raulduke

Banned

0-50 F
51-64 D
65-79 C
80-92 B
93-100 A

I have rearranged the columns in the array to have the grades be on column 1 and column 2 but both return an N/A error whether the column index number is 1 or 2.

#### etaf

Wayne
Moderator
so because you are using a range - you need to drop the last element of vlookup the true/false - so that you will get the closest value , and also needs to be sorted ascending

i have attached an example to help with the setup

the look-up for the grades setout as below and also us \$ to fix the range , so you can copy the formula down

0 F
50 F
51 D
64 D
65 C
79 C
80 B
92 B
93 A
100 A

#### Attachments

• 15 KB Views: 26

#### raulduke

Banned
Thanks, that worked, though it was an exercise that came with that grade table so I'm not sure if I am allowed to change it. Is there any way to make the VLOOKUP work while keeping that table intact, or is the table fundamentally flawed and will never work?

#### etaf

Wayne
Moderator
as its a homework question, we do not normally answer those questions, as part of the learning experience is to do the research yourself

#### raulduke

Banned
OK, first of all, that is a ridiculous policy. I understand you don't want kids coming and getting people to do all of their work for them, but I wouldn't have come here if I hadn't spent hours trying to get it to work with the given array intact to no avail. It was either ask someone for help or don't do it. I think it was pretty clear that I wasn't just copying and pasting my assignment and asking you all to do it for me, rather that I had one part that didn't make sense and I was looking to understand why. It seemed like it might be a "trick" question, and as the solution offered involved changing the given array, I think that was a fair assumption. As such, there was nothing I was going to learn from this besides "don't trust people." I did plenty of "research" on Google, but couldn't find an answer. Besides, what is the difference between having Google tell me the answer or somebody here? It is simply a more direct and reliable source.

I already sent it in, (I changed the table and wrote a polite note saying that the regular VLOOKUP function didn't work with the table provided and that I modified it to work better) so no help you all give me is going to change anything, I just wanted to see if I was right in my suspicion that this was a trick question, <removed by Moderator - ETAF>

#### etaf

Wayne
Moderator
see the rules , which your agreed to when you joined
Any disparaging or disrespectful remarks regarding this site and/or its Moderators, Administrators, rules and/or actions taken will not be tolerated in order to preserve the integrity and mutual respect within the community that we all enjoy.
Also if you had looked up vlookup on any of the MS forums / excel forums , that would have explained how to use the table -

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On