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

1. ## 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.  Register To Reply

2. ## 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"})  Register To Reply

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

another solution.  Register To Reply

4. ## Re: LOOK UP Formula? I need to return a score depending on the value of another cell. Originally Posted by benishiryo 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  Register To Reply

5. ## Re: LOOK UP Formula? I need to return a score depending on the value of another cell. Originally Posted by Dunc3142 another solution.
This one works as well and it's an intersting approach. I like it, thanks.  Register To Reply

6. ## 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.  Register To Reply

7. ## 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"  Register To Reply

8. ## 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.  Register To Reply

9. ## 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  Register To Reply

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

That is exactly what I did. Thanks!  Register To Reply

11. ## 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  Register To Reply

12. ## 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...   Register To Reply

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