VLOOKUP problem

Discussion in 'Business Applications' started by raulduke, Nov 5, 2011.

Not open for further replies.

Joined:
Nov 5, 2011
Messages:
4
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

2. etafModerator

Joined:
Oct 2, 2003
Messages:
65,408
First Name:
Wayne
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

Joined:
Nov 5, 2011
Messages:
4

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.

4. etafModerator

Joined:
Oct 2, 2003
Messages:
65,408
First Name:
Wayne
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

Attached Files:

File size:
15 KB
Views:
26

Joined:
Nov 5, 2011
Messages:
4
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?

6. etafModerator

Joined:
Oct 2, 2003
Messages:
65,408
First Name:
Wayne
as its a homework question, we do not normally answer those questions, as part of the learning experience is to do the research yourself

Joined:
Nov 5, 2011
Messages:
4
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>

8. etafModerator

Joined:
Oct 2, 2003
Messages:
65,408
First Name:
Wayne
see the rules , which your agreed to when you joined
Also if you had looked up vlookup on any of the MS forums / excel forums , that would have explained how to use the table -

As Seen On