1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

VLOOKUP problem

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

Thread Status:
Not open for further replies.
Advertisement
  1. raulduke

    raulduke Banned Thread Starter

    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. etaf

    etaf Moderator

    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
     
  3. raulduke

    raulduke Banned Thread Starter

    Joined:
    Nov 5, 2011
    Messages:
    4
    The range of grades is:

    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. etaf

    etaf Moderator

    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:

  5. raulduke

    raulduke Banned Thread Starter

    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. etaf

    etaf Moderator

    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
     
  7. raulduke

    raulduke Banned Thread Starter

    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. etaf

    etaf Moderator

    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 -
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1025627

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice