+ Reply to Thread
Results 1 to 12 of 12

LOOK UP Formula? I need to return a score depending on the value of another cell.

  1. #1
    Registered User
    Join Date
    07-13-2007
    Posts
    7

    LOOK UP Formula? I need to return a score depending on the value of another cell.

    I have an Excel spreadsheet with five columns, the number of rows can be infinite after the top row which consists of . Column A=Hours, B=Score, C=X, D=Y and E=Z. I would like to be able to enter hours into column A and have ther score of X,Y, Z or a dash (-) self populate. If the number of hours is less than 20 the score should be a dash, between 20-30 the score would be X, 30-40 =Y, over 40=Z. It seems to me that this shouldn't as much trouble as I'm having but I just can't figure it out. Any help would be greatly appreciated.

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,130

    Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

    hi RMMARTY. wow took you 5 years for your 1st post?

    not sure what you want with column C:E. but attaching a rough eg. note that your scores clashes. 20-30 is "X". 30-40 is "Y". so is 30 X or Y? adjust the formula in red accordingly. these numbers should be the minimum to get the scores. meaning 0 is the minimum to get "-", 20 to get "X", and so on
    =LOOKUP(A2,{0,20,30,40},{"-","X","Y","Z"})
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Valued Forum Contributor Dunc3142's Avatar
    Join Date
    09-05-2007
    Location
    Franklin OH
    MS-Off Ver
    2000 and 2007 and 2010 @ Home & Teach 2010
    Posts
    351

    Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

    another solution.
    Attached Files Attached Files
    Yes it will. we just have to figure out how...
    If I have helped you, PLEASE click the * and add to my Rep.
    Also, if the problem is SOLVED please mark it as so.

  4. #4
    Registered User
    Join Date
    07-13-2007
    Posts
    7

    Thumbs up Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

    Quote Originally Posted by benishiryo View Post
    hi RMMARTY. wow took you 5 years for your 1st post?

    not sure what you want with column C:E. but attaching a rough eg. note that your scores clashes. 20-30 is "X". 30-40 is "Y". so is 30 X or Y? adjust the formula in red accordingly. these numbers should be the minimum to get the scores. meaning 0 is the minimum to get "-", 20 to get "X", and so on
    =LOOKUP(A2,{0,20,30,40},{"-","X","Y","Z"})
    Thank you for the reply, this works

  5. #5
    Registered User
    Join Date
    07-13-2007
    Posts
    7

    Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

    Quote Originally Posted by Dunc3142 View Post
    another solution.
    This one works as well and it's an intersting approach. I like it, thanks.

  6. #6
    Registered User
    Join Date
    07-13-2007
    Posts
    7

    Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

    Thank you both for your replies, very helpful. But now what if we throw in a kink. If we added a rating column and A, B, or C will be the value. Depending on the rating the scores for X, Y, Z will vary. For instance the value of X would be 30-40 for A, 25-35 for B and 20-30 for C. The scores for Y and Z would follow in the same manner.

  7. #7
    Valued Forum Contributor
    Join Date
    02-05-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    564

    Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

    I'm not sure want you are looking for, but I'll try with 2 solution (see attachment).

    Actually I'm confused with the scores because it has intersection between score i.e 30-40 for A and 25-35 for B.
    So if the value is between 30-35 which will come up? "A" or "B"
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-13-2007
    Posts
    7

    Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

    Yes, dependant upon the rating. I just solved it by having a table feed the XYZ cells according to the rating. Then the score is determined by where the hours fall with in those cells and returns the XY or Z. Far more complicated then I had hoped and I'm betting theres an an equally complicated formula that will do the same thing. But by having the XYZ column fed from seperate table I think it will be easier to update if those numbers change. Confused? When I get back to work I'll see about attaching a copy of what I did. Thank you everyone and have a great weekend.

  9. #9
    Valued Forum Contributor
    Join Date
    02-05-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    564

    Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

    Add another solution: range value X, Y and Z on another sheet


    Regards
    SDCh
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    07-13-2007
    Posts
    7

    Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

    That is exactly what I did. Thanks!

  11. #11
    Registered User
    Join Date
    07-13-2007
    Posts
    7

    Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

    Thanks to everyone that replied. I did get this figured out and everyones help is greatly appreciated.

    Ray

  12. #12
    Registered User
    Join Date
    02-19-2013
    Location
    jeddah, saudiariabia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: LOOK UP Formula? I need to return a score depending on the value of another cell.

    Brilliant formula dear Dunc... by your formula I solved another problem of me based on same steps

    Thanks and keep it up...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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