+ Reply to Thread
Results 1 to 3 of 3

If formula that then looks up and assigns a value from a range....

  1. #1
    Registered User
    Join Date
    08-26-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    27

    If formula that then looks up and assigns a value from a range....

    I am trying to assign a student a grade (A, B, C etc.) dependent upon the score they get in an exam.

    However there are two types of exam that the students could have done (Higher and Foundation) and the grade boundaries are different for each test.

    I want the formula to recognise which test they have done by looking at what I have entered in a particular column (either H or F) and then assign a value accordingly...

    Here is the formula I had hoped would work:
    =IF(O3="H",[LOOKUP(P3,{42,38,33,29,26,24,0},{"*","A","B","C","D","E","F","G","U"})],[LOOKUP(P3,{36,32,28,25,22,0},{"C","D","E","F","G","U"})])

    but Excel returns 'the name that you entered is not valid' and then highlights LOOKUP.

    Any ideas?
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,180

    Re: If formula that then looks up and assigns a value from a range....

    You should remove the square brackets (2 pairs). Also, LOOKUP expects the values to be sorted the other way around, so you will need to reverse the order of the numbers and the corresponding grades.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    08-26-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: If formula that then looks up and assigns a value from a range....

    That worked perfectly, thank you very much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1