Results 1 to 7 of 7

Thread: Formula not returning correct values

  1. #1

    Formula not returning correct values



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all,
    I would like a little assistance with a grade sheet that I am currently working on. I have attached the workbook and I have highlighted the area (Blue) in the worksheet named ARRAY from which the formulae is supposed to search. I have changed the colour of the incorrect value that the formulae returns (red) and I have also shown the correct value that the formulae should return Violet/Yellow. I have tried numerous things. I just cant eem to figure out why the formulae stops searching at the incorrect value. Any assistance will be appreciated.flava 60-40 Degree Grade Sheet Redesigned December 2014.xls

  2. #2
    Hi, try changing the M$3: to N$3:

    i.e. =VLOOKUP(M14,ARRAY!N$3:W$11,HLOOKUP(T14,ARRAY!O$1:W$2,2,FALSE))

  3. #3

    Post

    Quote Originally Posted by Beamer View Post
    Hi, try changing the M$3: to N$3:

    i.e. =VLOOKUP(M14,ARRAY!N$3:W$11,HLOOKUP(T14,ARRAY!O$1:W$2,2,FALSE))
    Thanks, that returns a C+, however, according to the blue grid in the array I should be getting a c-. Any other tips are welcome.

  4. #4
    Ok, I found 2 things.
    Firstly your numbers for the Hlookup need to advance by 1 since column N is the 1st column of the vlookup and column W is the 10th. (i.e. A to E need to be 2 to 10, not 1 to 9)
    Secondly you need to add another False qualifier for the Vlookup as well as the Hlookup.

    i.e. =VLOOKUP(M14,ARRAY!N$3:W$11,HLOOKUP(T14,ARRAY!O$1:W$2,2,FALSE),FALSE)

    Hopefully that should get you up and running

  5. #5
    Quote Originally Posted by Beamer View Post
    Ok, I found 2 things.
    Firstly your numbers for the Hlookup need to advance by 1 since column N is the 1st column of the vlookup and column W is the 10th. (i.e. A to E need to be 2 to 10, not 1 to 9)
    Secondly you need to add another False qualifier for the Vlookup as well as the Hlookup.

    i.e. =VLOOKUP(M14,ARRAY!N$3:W$11,HLOOKUP(T14,ARRAY!O$1:W$2,2,FALSE),FALSE)

    Hopefully that should get you up and running
    Thanks. That works for that particular set of grades, however it does not work for others. puzzling.... The grid below shows what the sheet should achieve. Vertical = exam Horizontal = Coursework. B exam and A coursework should give a B+ but it is returning a B. In essence what I am trying to achieve, is that for whatever set of grades are achieved in the 60-40 grade sheet template, the formulae should search the array table and return the resultant wherever the two intersect. Appreciated. I have learnt a lot o far. Never knew about the second false argument.


    A B+ B B- C+ C C- D E
    1 2 3 4 5 6 7 8 9
    1 A A A B+ B+ B B B B- C+
    2 B+ B+ B+ B+ B B B- B- B- C+
    3 B B+ B B B B- B- C+ C+ C
    4 B- B B B- B- B- C+ C+ C C
    5 C+ B B- B- C+ C+ C+ C C C-
    6 C B- B- C+ C+ C C C C- D
    7 C- C+ C+ C+ C C C- C- C- E
    8 D C+ C C C C- D D D E
    9 E D D D D E E E E E

  6. #6
    I can't find an issue, if you changed all three things I pointed out, it seems to always give the right result.
    I'll attach my copy just incase you have a typo somewhere.
    I've added a few cells to the Array sheet just for testing purposes to imitate Cells M14,T14, & AC14 on the 2nd sheet.

    Edit: Actually I noticed in your grid above that the horizontal numbers still aren't fixed.

  7. #7

    Quote Originally Posted by Beamer View Post
    I can't find an issue, if you changed all three things I pointed out, it seems to always give the right result.
    I'll attach my copy just incase you have a typo somewhere.
    I've added a few cells to the Array sheet just for testing purposes to imitate Cells M14,T14, & AC14 on the 2nd sheet.

    Edit: Actually I noticed in your grid above that the horizontal numbers still aren't fixed.
    Thanks bro seems to be working fine now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •